Add these 5 Must-Have DMV Queries to your DBA Toolbelt!

The 5 DMV Queries Every DBA Should Have Ready

Dynamic Management Views are one of the most powerful diagnostic tools built into SQL Server. If you're not querying DMVs regularly, you're flying blind. These five queries give you immediate, actionable visibility into procedure execution frequency, query performance, missing indexes, wait statistics, and plan cache health - the kind of intelligence that separates reactive firefighting from proactive database management.

The original content for this article was incomplete, so what follows is a practical, field-tested set of DMV queries that DBA Services uses regularly when performing SQL Server health checks and performance investigations.

Before You Run These Queries: A Critical Warning About DBCC FREEPROCCACHE

Before diving in, there's one important preparation step worth understanding properly.

Some DBAs recommend running DBCC FREEPROCCACHE before executing these queries so that the "Age in Cache" values are consistent across all cached plans. The logic is sound: if plans were cached at different times, comparing their age becomes meaningless.

The problem is that DBCC FREEPROCCACHE clears the entire plan cache on the instance. On a production server, that means every subsequent query has to recompile from scratch. You'll see an immediate CPU spike and a temporary performance degradation as SQL Server rebuilds execution plans. On a busy OLTP system, this can cause real user-facing slowdowns for several minutes.

The practical recommendation: run these queries during a low-traffic window if you want consistent age comparisons, or simply accept that age values may vary and focus on the execution counts and performance metrics instead. Never run DBCC FREEPROCCACHE on production without understanding the consequences first.

Query 1: Which Stored Procedures Are Called Most Often?

Knowing your most-executed procedures is essential for baselining and troubleshooting. If a procedure runs 50,000 times per day and its average duration creeps up by 10 milliseconds, that's 500 extra seconds of cumulative wait time added to your workload. You need to know which procedures matter most before you can prioritise tuning efforts.

SELECT TOP 25
    DB_NAME(qt.dbid) AS DatabaseName,
    OBJECT_NAME(qt.objectid, qt.dbid) AS ProcedureName,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time_us,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time_us,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.cached_time,
    qs.last_execution_time
FROM sys.dm_exec_procedure_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
ORDER BY qs.execution_count DESC;

This query pulls from sys.dm_exec_procedure_stats, which accumulates statistics since the last plan cache entry was created. Pay attention to avg_elapsed_time_us and avg_logical_reads alongside execution count. A procedure that runs a million times with 2 logical reads per execution is fine. One that runs a million times with 50,000 logical reads per execution is a serious problem.

Query 2: Which Queries Are Consuming the Most CPU?

High CPU is one of the most common complaints DBAs get called in to investigate. This query surfaces the top offenders directly from the plan cache.

SELECT TOP 25
    qs.total_worker_time / qs.execution_count AS avg_cpu_time_us,
    qs.total_worker_time,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1) AS query_text,
    qp.query_plan,
    DB_NAME(qt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

Sort by total_worker_time to find the queries that have consumed the most cumulative CPU since they entered cache. Switch to avg_cpu_time_us if you want to find the worst individual offenders regardless of how often they run. Both views are useful depending on whether you're investigating sustained high CPU or intermittent spikes.

Query 3: What Missing Indexes Is SQL Server Recommending?

SQL Server tracks index recommendations automatically through the query optimiser. Every time the optimiser generates a plan and determines an index would have helped, it logs that information in sys.dm_db_missing_index_details. This query aggregates those recommendations by estimated impact.

SELECT TOP 25
    DB_NAME(mid.database_id) AS DatabaseName,
    mid.statement AS TableName,
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS estimated_improvement,
    migs.avg_user_impact AS avg_impact_pct,
    migs.user_seeks,
    migs.user_scans,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;

A word of caution here: SQL Server's missing index recommendations are suggestions, not instructions. The optimiser evaluates each query in isolation and doesn't account for the overhead of maintaining additional indexes, the impact on write performance, or whether similar indexes already exist with slightly different column ordering. Treat high-impact recommendations as a starting point for investigation, not an automatic to-do list.

Query 4: What Are Your Top Wait Statistics?

Wait statistics tell you what SQL Server is spending its time waiting for. This is arguably the single most useful starting point for any performance investigation. If you don't know what your server is waiting on, you're guessing at the cause of performance problems.

SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(5,2)) AS pct_of_total_waits
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT',
    'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
    'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'HADR_WORK_QUEUE',
    'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH',
    'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK', 'SLEEP_DBSTARTUP',
    'SLEEP_DCOMSTARTUP', 'SLEEP_MASTERDBREADY', 'SLEEP_MASTERMDREADY',
    'SLEEP_MASTERUPGRADED', 'SLEEP_MSDBSTARTUP', 'SLEEP_SYSTEMTASK',
    'SLEEP_TEMPDBSTARTUP', 'SNI_HTTP_ACCEPT', 'SP_SERVER_DIAGNOSTICS_SLEEP',
    'SQLTRACE_BUFFER_FLUSH', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    'WAITFOR', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

The excluded wait types are benign background waits that would otherwise dominate the results and obscure meaningful signal. Common wait types to pay attention to include PAGEIOLATCH_SH (storage I/O bottleneck), LCK_M_* (blocking and locking), CXPACKET or CXCONSUMER (parallelism issues), and SOS_SCHEDULER_YIELD (CPU pressure). Microsoft's documentation on wait statistics provides a comprehensive reference for interpreting each wait type.

Query 5: What Does Your Plan Cache Look Like?

Understanding your plan cache composition helps identify ad hoc query problems, single-use plan bloat, and whether "Optimise for Ad Hoc Workloads" should be enabled.

SELECT
    objtype AS PlanType,
    COUNT(*) AS PlanCount,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS TotalSizeMB,
    AVG(usecounts) AS AvgUseCount,
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS SingleUsePlans
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY TotalSizeMB DESC;

If you see thousands of single-use ad hoc plans consuming significant memory, that's a strong signal to enable the "Optimise for Ad Hoc Workloads" server configuration option. This setting causes SQL Server to store only a small stub on the first execution of an ad hoc query, rather than the full plan, which can dramatically reduce plan cache memory pressure on systems with high volumes of unparameterised queries.

Key Takeaways

• DMV queries are cumulative since the last service restart or plan cache clear. Always consider the time window when interpreting results, and note when the instance was last restarted.
• Running DBCC FREEPROCCACHE on production to "reset" statistics carries real performance risk. Understand the consequences before doing it.
• Missing index recommendations from sys.dm_db_missing_index_details are starting points for analysis, not automatic implementation candidates. Always validate them in context.
• Wait statistics from sys.dm_os_wait_stats are the fastest way to identify the category of a performance problem. Start here before diving into individual queries.
• A high count of single-use plans in the plan cache is a common and easily addressed memory pressure issue. Enable "Optimise for Ad Hoc Workloads" if this applies to your environment.

These five DMV queries form a solid foundation for day-to-day SQL Server monitoring and ad hoc investigation. That said, interpreting what you find and taking the right corrective action requires experience and context. If you'd like a structured review of your SQL Server environment, DBA Services offers comprehensive health checks that cover performance, security, configuration, and availability - giving you a clear picture of where your databases stand and what needs attention.