Top 10 Expensive Queries in user database

This blog post shares the script to get top 10 most expensive queries in the current database on SQL Server. It uses DMVs (mainly sys.dm_exec_query_stats) so it works on all support versions from SQL Server 2012 to SQL Server 2019, and it doesn't required you to enable any database features such as Query Store. You can modify it to add more top 10 figures that the dm_exec_query_stats DMV has. The results contain a query_plan column that you can save the XML content as a .sqlplan file so that it can be opened by SSMS in another computer to view the graphical execution plan of the expensive query. Here is the script:

-- Top 10 queries - total read

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_logical_reads, (qs.total_logical_reads / qs.execution_count) AS avg_logical_reads,

qs.last_logical_reads,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY qs.total_logical_reads DESC;

-- Top 10 queries - avg read

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_logical_reads, (qs.total_logical_reads / qs.execution_count) AS avg_logical_reads,

qs.last_logical_reads,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY avg_logical_reads DESC;

-- Top 10 queries - total write

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_logical_writes, (qs.total_logical_writes / qs.execution_count) AS avg_logical_writes,

qs.last_logical_writes,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY qs.total_logical_writes DESC;

-- Top 10 queries - avg write

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_logical_writes, (qs.total_logical_writes / qs.execution_count) AS avg_logical_writes,

qs.last_logical_writes,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY avg_logical_writes DESC;

-- Top 10 queries - total cpu time

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_worker_time, (qs.total_worker_time / qs.execution_count) AS avg_worker_time,

qs.last_worker_time,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY qs.total_worker_time DESC;

-- Top 10 queries - avg cpu time

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_worker_time, (qs.total_worker_time / qs.execution_count) AS avg_worker_time,

qs.last_worker_time,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY avg_worker_time DESC;

-- Top 10 queries - total memory grant

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_grant_kb, (qs.total_grant_kb / qs.execution_count) AS avg_grant_kb,

qs.last_grant_kb,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY qs.total_grant_kb DESC;

-- Top 10 queries - avg memory grant

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_grant_kb, (qs.total_grant_kb / qs.execution_count) AS avg_grant_kb,

qs.last_grant_kb,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY avg_grant_kb DESC;