CONTACT US

How to find slow SQL queries using DMVs in SQL Server?

  • 2 Comments

What is Dynamic Management Views (DMVs)?

Introduced within SQL Server 2005, SQL Dynamic Management Views (DMVs) are significant features of SQL Server.

There are several DMVs that provide data about execution plans, query stats, recent queries and so on.

These can be used together to determine what is going on with a SQL Server instance.

In this post, I will list some useful queries that may help you identify where your resources have been spent and much more.

By the way, this check is a part of our SQL Server Health Check service.

NOTE: The queries below apply to: SQL Server 2014 (12.x) and later.

Top TSQL by reads

Storage reads are by far slowest operation SQL can do.

Therefore, when tuning, concentrating on TSQL calls that cost the most logical reads often makes sense.

If storage access is reduced, then SQL needs less CPU, and the Duration of the query improves.

Run the query below to get the top 10 most expensive TSQL calls – by logical (storage) reads:

  1. SELECT TOP(10) DB_NAME(t.[dbid]) AS [Database],
  2. REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [ShortQueryTXT], 
  3. qs.total_logical_reads AS [TotalLogicalReads],
  4. qs.min_logical_reads AS [MinLogicalReads],
  5. qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
  6. qs.max_logical_reads AS [MaxLogicalReads],   
  7. qs.min_worker_time AS [MinWorkerTime],
  8. qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
  9. qs.max_worker_time AS [MaxWorkerTime], 
  10. qs.min_elapsed_time AS [MinElapsedTime], 
  11. qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime], 
  12. qs.max_elapsed_time AS [MaxElapsedTime],
  13. qs.execution_count AS [ExecutionCount], 
  14. CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%%' THEN 1 ELSE 0 END AS [HasMissingIX],
  15. qs.creation_time AS [CreationTime]
  16. ,t.[text] AS [Complete Query Text], qp.query_plan AS [QueryPlan]
  17. FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
  18. CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
  19. CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
  20. ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE)

Top TSQL by CPU

Total Worker Time – means total CPU cycles consumed.

Run the query below to get the top 10 most expensive TSQL CPU consumers.

  1. SELECT TOP(10) DB_NAME(t.[dbid]) AS [Database], 
  2. REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [ShortQueryText],  
  3. qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [MinWorkerTime],
  4. qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
  5. qs.max_worker_time AS [MaxWorkerTime], 
  6. qs.min_elapsed_time AS [MinElapsedTime], 
  7. qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime], 
  8. qs.max_elapsed_time AS [MaxElapsedTime],
  9. qs.min_logical_reads AS [MinLogicalReads],
  10. qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
  11. qs.max_logical_reads AS [MaxLogicalReads], 
  12. qs.execution_count AS [ExecutionCount],
  13. CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%%' THEN 1 ELSE 0 END AS [HasMissingIX], 
  14. qs.creation_time AS [CreationTime]
  15. ,t.[text] AS [Query Text], qp.query_plan AS [QueryPlan]
  16. FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
  17. CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
  18. CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
  19. ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE)

Top TSQL by execution count

Run the query below to get the top 10 most  TSQL calls.

  1. SELECT TOP(10) LEFT(t.[text], 50) AS [ShortQueryText],
  2. qs.execution_count AS [ExecutionCount],
  3. qs.total_logical_reads AS [TotalLogicalReads],
  4. qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
  5. qs.total_worker_time AS [TotalWorkerTime],
  6. qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
  7. qs.total_elapsed_time AS [TotalElapsedTime],
  8. qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
  9. CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%%' THEN 1 ELSE 0 END AS [HasMissingIX], 
  10. qs.creation_time AS [CreationTime]
  11. ,t.[text] AS [CompleteQueryText], 
  12. qp.query_plan AS [Query Plan] 
  13. FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
  14. CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
  15. CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
  16. WHERE t.dbid = DB_ID()
  17. ORDER BY [ExecutionCount] DESC OPTION (RECOMPILE)

Top SPs by average variable time

Stored procedure average elapsed variable time.

This show that sometimes query is fast and sometimes it is slow.

Often this means that bad query plan is cached and when SP reruns it used bad plan.

This maybe be easy to tune.

Run the query below to get the top 10 stored procedures by average variable time:

  1. SELECT TOP(10) p.name AS [SPName],
  2. qs.min_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime], 
  3. qs.max_elapsed_time, qs.last_elapsed_time, qs.total_elapsed_time, qs.execution_count, 
  4. ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute], 
  5. qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
  6. qs.total_worker_time AS [TotalWorkerTime],
  7. CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%%' THEN 1 ELSE 0 END AS [HasMissingIX],
  8. FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [LastExecutionTime], 
  9. FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [PlanCachedTime]
  10. ,qp.query_plan AS [QueryPlan]
  11. FROM sys.procedures AS p WITH (NOLOCK)
  12. INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
  13. ON p.[object_id] = qs.[object_id]
  14. CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
  15. WHERE qs.database_id = DB_ID()
  16. AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
  17. ORDER BY [AvgElapsedTime] DESC OPTION (RECOMPILE)

Top SPs by CPU usage

Total Worked Time– is total CPU cost consumed by that store procedure since SQL Engine restart.

Run the query below to get the top 10 most expensive stored procedures by CPU.

  1. SELECT TOP(10) p.name AS [SPName], 
  2. qs.total_worker_time AS [TotalWorkerTime], 
  3. qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
  4. qs.execution_count AS [ExecutionCount], 
  5. ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
  6. qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
  7. CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%%' THEN 1 ELSE 0 END AS [HasMissingIX],
  8. FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [LastExecutionTime], 
  9. FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [PlanCachedTime]
  10. ,qp.query_plan AS [Query Plan]
  11. FROM sys.procedures AS p WITH (NOLOCK)
  12. INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
  13. ON p.[object_id] = qs.[object_id]
  14. CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
  15. WHERE qs.database_id = DB_ID()
  16. AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
  17. ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE)

Top SPs By Execution Count

Run the query below to get the top 10 most executed stored procedures.

  1. SELECT TOP(10) p.name AS [SPName], 
  2. qs.execution_count AS [ExecutionCount],
  3. ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
  4. qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
  5. qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],    
  6. qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
  7. CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%%' THEN 1 ELSE 0 END AS [HasMissingIX],
  8. FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [LastExecutionTime], 
  9. FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [PlanCachedTime]
  10. ,qp.query_plan AS [QueryPlan]
  11. FROM sys.procedures AS p WITH (NOLOCK)
  12. INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
  13. ON p.[object_id] = qs.[object_id]
  14. CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
  15. WHERE qs.database_id = DB_ID()
  16. AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
  17. ORDER BY [ExecutionCount] DESC OPTION (RECOMPILE)

Top SPs by I/O

This shows which stored procs cause most I/O.

Run the query below to get the top 10 most expensive store procudere calls – by Average I/O.

  1. SELECT TOP(10) OBJECT_NAME(qt.objectid, dbid) AS [SPName],
  2. (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [AvgIO], 
  3. qs.execution_count AS [ExecutionCount],
  4. SUBSTRING(qt.[text],qs.statement_start_offset/2,
  5. (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
  6. ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS [QueryText]	
  7. FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
  8. CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
  9. WHERE qt.[dbid] = DB_ID()
  10. ORDER BY [AvgIO] DESC OPTION (RECOMPILE)

More information:

Use DMVs to Determine Usage Statistics and Performance of Views

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

2 Responses

Leave a Reply

Your email address will not be published. Required fields are marked *