Optimizing SQL Server Performance: Identifying Resource-Intensive Queries
Ever wonder which queries are hogging your SQL Server's resources? This handy SQL script helps you pinpoint the culprits by showing you the top 50 most resource-intensive queries based on logical reads. It's a fantastic way to quickly identify areas for optimization and improve your database's overall performance.
Here's the SQL query:
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_logical_reads AS [Total Logical Reads] ,qs.min_logical_reads AS [Min Logical Reads] ,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads] ,qs.max_logical_reads AS [Max Logical Reads] ,qs.min_worker_time AS [Min Worker Time] ,qs.total_worker_time / qs.execution_count AS [Avg Worker Time] ,qs.max_worker_time AS [Max Worker Time] ,qs.min_elapsed_time AS [Min Elapsed Time] ,qs.total_elapsed_time / qs.execution_count AS [Avg Elapsed Time] ,qs.max_elapsed_time AS [Max Elapsed Time] ,qs.execution_count AS [Execution Count] ,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_logical_reads DESC OPTION (RECOMPILE);
This script leverages two powerful Dynamic Management Views (DMVs) in SQL Server:
sys.dm_exec_query_plan: This DMV retrieves the query plan for a given plan handle, allowing you to see if any missing indexes are suggested.sys.dm_exec_query_stats: This DMV provides aggregated performance statistics for cached query plans, including logical reads, worker time, and elapsed time.
By using these DMVs together, you get a comprehensive view of your most demanding queries, helping you make informed decisions about indexing and query tuning. Give it a try and see what insights you can uncover!
