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

SQL SERVER - Long Running Queries with Execution Plan long-running-queries-800x163

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)

, ,
Previous Post
SQL SERVER – sp_updatestats Performance and Disabled Nonclustered Indexes
Next Post
SQL SERVER – Fill Factor – Instance Level or Index Level

Related Posts

7 Comments. Leave new

  • think you are missing a column name: “t. AS [Complete Query Text]”

    Reply
  • I think there’s a little typo, Pinal…
    You have “t. AS [Complete Query Text]…” and I think it should be “t.text AS [Complete Query Text]…”

    Reply
  • Madhur Narula
    July 9, 2021 10:06 am

    Getting syntax error in order by on the last three lines

    Reply
  • how to resolve deadlocks

    How to resolve a long running query
    how to analyse where we need a index to resolve long execution
    if a query is running more than 21mins like that
    please these are the questions i have i hope you clear my doubts and help me alot.

    Reply
  • Hi Dave,
    how to resolve deadlocks

    how to resolve the long running queries and how to understand where we need to create an index
    execution min is taking more than 21 mins and extra
    please help me through this.

    Reply

Leave a Reply