SQL SERVER – List Expensive Queries – Updated March 2021

One of my favorite scripts which I discuss with my client during Comprehensive Database Performance Health Check is how to get a list of Expensive Queries.

SQL SERVER - List Expensive Queries - Updated March 2021 ExpensiveQueries-800x577

If you have watched my following video, you probably know that it is easy to tune query if you know what resources, you want to tune for.


Here is the query which will help you find queries expensive for various resources – CPU, IO as well as for Elapsed time. If you pay attention there are a few commented ORDER BY clauses, you can use each of them to find resource expensive queries.

SELECT TOP(50) qs.execution_count AS [Execution Count],
(qs.total_logical_reads)*8/1024.0 AS [Total Logical Reads (MB)],
(qs.total_logical_reads/qs.execution_count)*8/1024.0 AS [Avg Logical Reads (MB)],
(qs.total_worker_time)/1000.0 AS [Total Worker Time (ms)],
(qs.total_worker_time/qs.execution_count)/1000.0 AS [Avg Worker Time (ms)],
(qs.total_elapsed_time)/1000.0 AS [Total Elapsed Time (ms)],
(qs.total_elapsed_time/qs.execution_count)/1000.0 AS [Avg Elapsed Time (ms)],
qs.creation_time AS [Creation Time]
,t.text AS [Complete Query Text], qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE t.dbid = DB_ID()
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);-- frequently ran query
-- ORDER BY [Total Logical Reads (MB)] DESC OPTION (RECOMPILE);-- High Disk Reading query
-- ORDER BY [Avg Worker Time (ms)] DESC OPTION (RECOMPILE);-- High CPU query
-- ORDER BY [Avg Elapsed Time (ms)] DESC OPTION (RECOMPILE);-- Long Running query

Once you find the expensive queries based on your requirement, you can go to the last column of this query and open the execution plan and start tuning the query. If you need further help you can always reach out to me or just post comments.

Here are my few recent videos and I would like to know what is your feedback about them. You can subscribe to my youtube channel here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL CPU, SQL Memory, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Data and Index Compression – Real-World Conversation
Next Post
SQL SERVER – Flush Data from Memory to Disk

Related Posts

3 Comments. Leave new

  • Hi Pinal,

    I’m confused when you divide qs.total_logical_reads by 1000, and alias the column with “ms” included, which suggests milliseconds. I can see doing that for qs.total_worker_time and qs.total_elapsed_time, but the number of logical reads is not a measure of time.

    Reply
  • Not really a big deal, but something I have started doing when I get the SQL Text from DMVs is convert it to XML. That way you can click on it and preview it easier.:

    , CAST(” AS xml) AS [Complete Query Text]

    Reply
  • how often should this be run or is it one time?

    Reply

Leave a Reply