Ever wondered which queries are hogging resources in your SQL Server database? This handy SQL script can help you identify those troublesome queries by showing you the top 50 based on average elapsed time. It's a fantastic way to quickly pinpoint performance bottlenecks!
SELECT TOP (50) DB_NAME(t.[dbid]) AS [Database Name] ,REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10), ''), CHAR(13), '') AS [Short Query Text] ,qs.total_elapsed_time / qs.execution_count AS [Avg Elapsed Time] ,qs.min_elapsed_time ,qs.max_elapsed_time ,qs.last_elapsed_time ,qs.execution_count AS [Execution Count] ,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads] ,qs.total_physical_reads / qs.execution_count AS [Avg Physical Reads] ,qs.total_worker_time / qs.execution_count AS [Avg Worker Time] ,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index] ,qs.creation_time AS [Creation Time] --,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel 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 ORDER BY qs.total_elapsed_time / qs.execution_count DESC OPTION (RECOMPILE);
This script leverages two powerful SQL Server Dynamic Management Views (DMVs):
sys.dm_exec_query_plan: This DMV provides the execution plan for a query, which is crucial for understanding how SQL Server intends to run your queries and identifying potential issues.sys.dm_exec_query_stats: This DMV returns aggregated performance statistics for cached query plans. It's your go-to for seeing how queries have performed over time.
By combining data from these two DMVs, the script gives you a comprehensive overview of your top-performing (or rather, top-resource-consuming) queries, including metrics like average elapsed time, logical and physical reads, and even whether a missing index is suggested in the query plan. This can be an invaluable tool for any database administrator or developer looking to optimize SQL Server performance!
