7 Essential SQL Server DMV Queries Every Admin Should Know

Did You Know? SQL Server queries can drive CPU usage to 90% – and stay there.

In a Microsoft case study, unoptimized T-SQL caused CPU spikes on a production server during peak load. For CTOs, DBAs, and IT managers, that’s more than a performance issue – it’s a bottleneck that can derail your entire system.

The fix? Dynamic Management Views (DMVs).

DMVs expose exactly where your SQL Server is struggling – from high-CPU queries to missing indexes and bloated execution plans.

This guide gives you 7 must-have DMV queries the pros use to spot performance killers and quickly free up SQL Server headroom.

What are Dynamic Management Views (DMVs)?

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

Several DMVs 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 DMV queries 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.

Useful DMV Queries: Examples and Scenarios

DMVs deliver a clear view into SQL Server performance. They pinpoint resource-heavy queries and execution patterns that slow your system.

For SQL Server experts, this is actionable insight, not just data.

Below, we’ll cover T-SQL execution, stored procedure performance, and I/O usage. These scripts and scenarios offer a precise way to tackle bottlenecks.

Before You Run These DMV Queries:

• These scripts use WITH (NOLOCK) to reduce blocking, but results may include uncommitted or inconsistent data.
• The cached_time filter (DATEDIFF(...) > 0) helps exclude overly fresh plans, but you may need to adjust or remove it based on your workload and how recently SQL Server was restarted.

1. Top T-SQL by reads

Storage reads are by far the slowest SQL operation. 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 query duration 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)

2. Top T-SQL by CPU

Total Worker Time refers to total CPU cycles consumed. As high CPU-consuming queries can slow down the system, it is essential to identify queries with substantial CPU consumption.

DMV-based insights depend on the in-memory execution cache. If the server restarts or memory pressure clears the cache, query stats may be lost.

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)

3. Top T-SQL by execution count

Running the same queries over and over can quietly drain SQL Server resources, even if each execution seems lightweight.

Run the query below to get the Top 10 most executed 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)

4. 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)

5. Top Stored Procedures (SPs) by CPU Usage

Identify the stored procedures that consume the most CPU with Total Worked Time – the total CPU cost consumed since the 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)

6. Top Stored Procedures (SPs) By Execution Count

Stored procedures with high execution counts can be a sign of excessive resource consumption or inefficient query logic.

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)

7. This shows which stored procs cause the most I/O.

Understanding which stored procedures cause the most I/O is essential for performance tuning.

Run the query below 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)

Bonus Tip: Identify Missing Indexes (Use With Caution)

Once the most resource-intensive stored procedures are identified, it’s tempting to start tuning immediately, and one common path is through indexing. SQL Server offers built-in views that can help highlight potentially missing indexes, which might improve query performance.

Here’s a query to identify missing indexes, prioritized by their estimated impact:

SELECT migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS improvement_score
	,mid.statement AS table_name
	,mid.equality_columns
	,mid.inequality_columns
	,mid.included_columns
	,migs.user_seeks
	,migs.user_scans
	,migs.last_user_seek
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY improvement_score DESC

While this DMV is useful, it’s not a direct recommendation to create indexes blindly. Each suggested index should be carefully evaluated. Ask:

• Is the query it’s optimizing executed frequently throughout the day?
• Does the estimated improvement justify the added write cost and index maintenance?
• Would a composite index already cover this scenario?

Important Notes on DMV Accuracy

Keep in Mind: These Queries Depend on the Plan Cache

The DMV queries above rely on SQL Server’s execution plan cache (dm_exec_query_stats, dm_exec_procedure_stats). That means:

• If the server restarts or DBCC FREEPROCCACHE is run, this data resets.
  Stale or unused plans may get aged out over time, depending on memory pressure.
• Frequently executed procedures with reused but poor plans (e.g., due to parameter sniffing) can skew averages.

💡 Pro Tip: If you’re doing performance analysis, run these DMV queries during peak hours and before plan cache resets, to get a more accurate snapshot.

Tools and Permissions for DMV Queries in SSAS

To effectively monitor and optimize your SQL Server Analysis Services (SSAS) instance, it’s essential to use the right tools and understand the permissions required to query DMVs.
Available Tools for Running DMV Queries:

• SQL Server Management Studio (SSMS) – Ideal for ad-hoc DMV queries, troubleshooting, and live performance checks.
• PowerShell (with AMO/ADOMD.NET) – Best for automated querying, scheduled data pulls, and multi-instance reporting.
• Third-Party Tools – Tools like Redgate, SentryOne, and SolarWinds offer visual dashboards and alerting built on DMV data.

Permissions Needed for Access:

• Server Administrator – Full access to all DMV data across the SSAS instance.
• Database Administrator – Can query DMVs only within their assigned SSAS database.
• Read-Only User – Limited to DMV data for authorized objects. Requires ReadDefinition permission.

<Role>
  <Name>ReadOnlyDMVAccess</Name>
  <Permissions>
    <DatabasePermission Access="ReadDefinition" />
  </Permissions>
</Role>

Additional Resources

• Use DMVs to Determine Usage Statistics and Performance of Views
• For a broader perspective on monitoring SQL Server performance, check out our Best Practices For Performance Counters in Microsoft SQL Server.

Optimize SQL Server Health with DMVs

Database administrators must understand and use DMVs in SQL Server to boost performance, diagnose issues, and improve efficiency. Mastering these queries helps you uncover hidden bottlenecks, monitor resource usage, and fine-tune SQL Server operations for better system health.

Ready to take your SQL Server skills to the next level? Visit Red9 to explore more resources, expert insights, and actionable guides designed to help you excel in SQL Server management and beyond.

Frequently Asked Questions