What are Dynamic Management Views (DMVs)?
Introduced within SQL Server 2005, SQL Dynamic Management Views (DMVs) are significant features of SQL Server.
Several DMV 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 for DMVs in SQL Server that may help you identify where your resources have been spent and much more.
Note: the DMV queries below apply to SQL Server 2014 (12.x) and later versions.
Top T-SQL by reads
Storage reads are by far the slowest operation SQL can do.
Therefore, when tuning, concentrating on T-SQL 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 T-SQL 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 T-SQL by CPU
Total Worker Time – means total CPU cycles consumed.
Run the query below to get the Top 10 most expensive T-SQL 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 T-SQL by execution count
Run the query below to get the Top 10 most T-SQL 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 stored procedures (SPs) by average variable time
Stored procedure: average elapsed variable time.
This shows that sometimes the query is fast and sometimes it is slow.
Often, this means that a bad query plan is cached, and when the stored procedure reruns, it uses a bad plan.
This may be easy to tune.
Run the query below to get the Top 10 stored procedures by average variable 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 Worked Time – is the 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.
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 query below to get the Top 10 most 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 Input/Output (I/O)
This shows which stored procs cause the most I/O.
Run the query below to get the Top 10 most expensive store 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)
this blog is very nice & appreciate your efforts.
thank you!