TABLE OF CONTENTS
- •How to Find Slow SQL Queries in SQL Server
- •What Are Dynamic Management Views (DMVs)?
- •Top TSQL by reads
- •Top TSQL by CPU
- •Top TSQL by execution count
- •Top SPs by Average Elapsed Time
- •Need expert support for SQL Server?
- •Top SPs by CPU usage
- •Top SPs By Execution Count
- •Top SPs by I/O
- •Are You Confident in Your SQL Server Performance?
How to Find Slow SQL Queries in SQL Server
To find slow SQL queries, SQL Server gives you Dynamic Management Views (DMVs) that expose exactly where time and resources are being spent.
What Are Dynamic Management Views (DMVs)?
Dynamic Management Views (DMVs), introduced in SQL Server 2005, are one of SQL Server’s most important features.
There are several DMVs that provide data about execution plans, query statistics, recent queries, and more. They can be used together to determine what’s happening inside a SQL Server instance.
In this post, I’ll list some useful queries that can help you identify where your resources are being spent, and much more.
NOTE: These queries apply to SQL Server 2014 (12.x) and later.
Top TSQL by reads
Storage reads are the slowest operation SQL can perform. For this reason, when tuning, it usually makes sense to focus on the TSQL calls causing the most logical reads.
If storage access is reduced, SQL needs less CPU and the query’s duration increases as a side effect of comparison, so this metric is worth tracking closely.
Run the following query to get the top 10 most expensive TSQL calls by logical (storage) reads:
SELECT TOP(10) DB_NAME(t.[dbid]) AS [Database],
REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [ShortQueryTXT],
qs.total_logical_reads AS [TotalLogicalReads],
qs.min_logical_reads AS [MinLogicalReads],
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
qs.max_logical_reads AS [MaxLogicalReads],
qs.min_worker_time AS [MinWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.max_worker_time AS [MaxWorkerTime],
qs.min_elapsed_time AS [MinElapsedTime],
qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
qs.max_elapsed_time AS [MaxElapsedTime],
qs.execution_count AS [ExecutionCount],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%%' THEN 1 ELSE 0 END AS [HasMissingIX],
qs.creation_time AS [CreationTime]
,t.[text] AS [Complete Query Text], qp.query_plan AS [QueryPlan]
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)Top TSQL by CPU
Total Worker Time means the highest total CPU cycles consumed.
Run the following query to find the top 10 most expensive TSQL CPU consumers.
SELECT TOP(10) DB_NAME(t.[dbid]) AS [Database],
REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [ShortQueryText],
qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [MinWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.max_worker_time AS [MaxWorkerTime],
qs.min_elapsed_time AS [MinElapsedTime],
qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
qs.max_elapsed_time AS [MaxElapsedTime],
qs.min_logical_reads AS [MinLogicalReads],
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
qs.max_logical_reads AS [MaxLogicalReads],
qs.execution_count AS [ExecutionCount],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%%' THEN 1 ELSE 0 END AS [HasMissingIX],
qs.creation_time AS [CreationTime]
,t.[text] AS [Query Text], qp.query_plan AS [QueryPlan]
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_worker_time DESC OPTION (RECOMPILE)Top TSQL by execution count
Run the following query to get the top 10 most frequently executed TSQL calls.
SELECT TOP(10) LEFT(t.[text], 50) AS [ShortQueryText],
qs.execution_count AS [ExecutionCount],
qs.total_logical_reads AS [TotalLogicalReads],
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_elapsed_time AS [TotalElapsedTime],
qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%%' THEN 1 ELSE 0 END AS [HasMissingIX],
qs.creation_time AS [CreationTime]
,t.[text] AS [CompleteQueryText],
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 [ExecutionCount] DESC OPTION (RECOMPILE)Top SPs by Average Elapsed Time
This shows queries that are sometimes fast and sometimes slow.
This usually means a bad query plan has been cached, and the stored procedure keeps using that bad plan on subsequent executions.
This can often be an easy fix.
Need expert support for SQL Server?
Our senior database team supports SQL Server performance tuning, health checks, migrations, Remote DBA services and urgent operational issues.
Run the following query to get the top 10 stored procedures by average elapsed time:
SELECT TOP(10) p.name AS [SPName],
qs.min_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
qs.max_elapsed_time, qs.last_elapsed_time, qs.total_elapsed_time, qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_worker_time AS [TotalWorkerTime],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%%' THEN 1 ELSE 0 END AS [HasMissingIX],
FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [LastExecutionTime],
FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [PlanCachedTime]
,qp.query_plan AS [QueryPlan]
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY [AvgElapsedTime] DESC OPTION (RECOMPILE)Top SPs by CPU usage
Total Worker Time is the total CPU cost consumed by this stored procedure since the SQL Engine was last restarted.
Run the following query to get the top 10 most expensive stored procedures by CPU.
SELECT TOP(10) p.name AS [SPName],
qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.execution_count AS [ExecutionCount],
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%%' THEN 1 ELSE 0 END AS [HasMissingIX],
FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [LastExecutionTime],
FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [PlanCachedTime]
,qp.query_plan AS [Query Plan]
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE)Top SPs By Execution Count
Run the following query to get the top 10 most frequently executed stored procedures.
SELECT TOP(10) p.name AS [SPName],
qs.execution_count AS [ExecutionCount],
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%%' THEN 1 ELSE 0 END AS [HasMissingIX],
FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [LastExecutionTime],
FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [PlanCachedTime]
,qp.query_plan AS [QueryPlan]
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY [ExecutionCount] DESC OPTION (RECOMPILE)Top SPs by I/O
This shows which stored procedures are causing the most I/O.
Run the following query to get the top 10 most expensive stored procedure calls by average I/O.
SELECT TOP(10) OBJECT_NAME(qt.objectid, dbid) AS [SPName],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [AvgIO],
qs.execution_count AS [ExecutionCount],
SUBSTRING(qt.[text],qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS [QueryText]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [AvgIO] DESC OPTION (RECOMPILE)
Related Reading
SQL Server Performance Consulting
Are You Confident in Your SQL Server Performance?
Aryasoft analyzes your query performance, indexing, memory, and disk configuration end to end and delivers concrete improvement steps.
