One of the common challenges faced by database administrators is identifying long running queries in SQL Server. These queries can have a significant impact on the performance of the database and need to be optimized for better efficiency. In this blog post, we will discuss a simple script that can help you list all the long running queries in your database along with their execution plans.

The Script

Here is the script that you can use to identify 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);

The script retrieves information from the sys.dm_exec_query_stats dynamic management view, which contains statistics about query execution in SQL Server. It also uses the sys.dm_exec_sql_text and sys.dm_exec_query_plan functions to retrieve the complete query text and query plan, respectively.

Interpreting the Results

The script provides several columns of information for each long running query:

• Execution Count: The number of times the query has been executed.
• Total Logical Reads: The total number of logical reads performed by the query.
• Avg Logical Reads: The average number of logical reads per execution of the query.
• Total Worker Time: The total CPU time consumed by the query.
• Avg Worker Time: The average CPU time per execution of the query.
• Total Elapsed Time: The total elapsed time for the query.
• Avg Elapsed Time: The average elapsed time per execution of the query.
• Creation Time: The time when the query plan was created.
• Complete Query Text: The full text of the query.
• Query Plan: The execution plan for the query.

Considerations

It is important to note that the data retrieved by this script is from the cache. If a query is not in the cache, you will not be able to find its data using this script. Additionally, if your cache is large, it may take some time for the script to return results.

While running this script does not lock many resources, it is possible that it may take several minutes to return results from a large cache. Therefore, it is recommended to use this script during off-peak hours or when the database load is low.

Conclusion

Identifying long running queries is crucial for optimizing the performance of your SQL Server database. The script provided in this blog post can help you easily identify and analyze these queries, allowing you to take appropriate actions for improving the overall performance of your database.

If you found this blog post helpful, please leave a comment and let me know your thoughts. If you would like to see a video demonstration of this topic, feel free to mention it in the comments as well.