SQL SERVER – Long Running Queries with Execution Plan

One of the most frequently asked questions I get during my Comprehensive Database Performance Health Check is that if I have a simple script that will list all the long running queries in the database with the execution plan. Well, the answer is yes. Let us look at the script today.

Long Running Queries

SELECT TOP(50) qs.execution_count AS [Execution Count],
(qs.total_logical_reads)/1000.0 AS [Total Logical Reads in ms],
(qs.total_logical_reads/qs.execution_count)/1000.0 AS [Avg Logical Reads in ms],
(qs.total_worker_time)/1000.0 AS [Total Worker Time in ms],
(qs.total_worker_time/qs.execution_count)/1000.0 AS [Avg Worker Time in ms],
(qs.total_elapsed_time)/1000.0 AS [Total Elapsed Time in ms],
(qs.total_elapsed_time/qs.execution_count)/1000.0 AS [Avg Elapsed Time in 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);-- for frequently ran query
-- ORDER BY [Avg Logical Reads in ms] DESC OPTION (RECOMPILE);-- for High Disk Reading query
-- ORDER BY [Avg Worker Time in ms] DESC OPTION (RECOMPILE);-- for High CPU query
-- ORDER BY [Avg Elapsed Time in ms] DESC OPTION (RECOMPILE);-- for Long Running query

Well, the script is self-explaining and simple. I have also provided a few more orders if you want results based on different parameters. However, please remember the data which you are going to get is from the cache and if the query is not in the cache, it is quite possible you will be not able to find the query data.

One more thing to remember is that if your cache is large, it will take time for this query to run. While this query is not going to lock much of your resources, you should remember that it may take minutes of time to return results from the cache when it is huge.

Let me know what you think of this blog post. If you want, I can also create a SQL in Sixty Seconds video on this topic of long running queries. Just leave a comment.

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