SQL Server Performance Tuning

Top 7 DMV Queries Every SQL Server Admin Should Know

Mark Varnas
2 comments

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)

More information

Article by
Mark Varnas
Founder | CEO | SQL Veteran
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 thoughts on “Top 7 DMV Queries Every SQL Server Admin Should Know”

Comments are closed.

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials