Uncovering Your Most Frequent and Resource-Intensive SQL Queries
Ever wonder which queries are truly hammering your SQL Server? Or which ones are eating up the most resources? This handy T-SQL script helps you pinpoint the top 50 queries based on their execution count, along with crucial performance metrics like logical reads and worker time.
SQL Script to Analyze Query Performance
Here's the SQL query that will give you insights into your database's performance:
SELECT TOP (50) LEFT(t.[text], 50) AS [Short Query Text] ,qs.execution_count AS [Execution Count] ,qs.total_logical_reads AS [Total Logical Reads] ,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads] ,qs.total_worker_time AS [Total Worker Time] ,qs.total_worker_time / qs.execution_count AS [Avg Worker Time] ,qs.total_elapsed_time AS [Total Elapsed Time] ,qs.total_elapsed_time / qs.execution_count AS [Avg Elapsed 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 WHERE t.dbid = DB_ID() ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
Understanding the Metrics
This script leverages two powerful SQL Server Dynamic Management Views (DMVs):
sys.dm_exec_query_stats: Provides aggregate performance statistics for cached query plans.sys.dm_exec_query_plan: Returns the showplan in XML format for the batch specified by the plan handle.
By analyzing the output, you can quickly identify queries that are frequently executed, have high logical reads (indicating significant I/O operations), or consume a lot of worker time (CPU usage). The "Has Missing Index" column is particularly useful for spotting potential performance bottlenecks that could be resolved with a new index.
