sys.dm_exec_query_stats - Usage & Examples

sys.dm_exec_query_stats maintains a hash table in memory that stores aggregated execution statistics for each cached query plan. When the query optimizer creates or reuses an execution plan, SQL Server updates counters in this DMV including execution_count, total_worker_time, total_logical_reads, and total_elapsed_time.

The statistics accumulate from the first execution until the plan ages out of the procedure cache or gets explicitly removed. SQL Server 2016 introduced query_hash and query_plan_hash columns to group similar queries with different literal values. SQL Server 2019 added last_dop to track the degree of parallelism used in the most recent execution.

The DMV data persists only while plans remain in the procedure cache. Plan cache pressure from memory constraints, DBCC FREEPROCCACHE commands, or server restarts reset these statistics. Each row represents a unique combination of plan_handle and statement_start_offset, meaning different statements within the same stored procedure or batch appear as separate rows.

Memory grants and spill information became available in SQL Server 2016 through columns like total_spills and total_grant_kb. The statistics reflect cumulative values across all executions, requiring calculation of averages by dividing totals by execution_count for meaningful per-execution metrics.

-- Top CPU consuming queries by total worker time
SELECT TOP 20
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset 
          WHEN -1 THEN DATALENGTH(st.text) 
          ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

-- Queries with highest logical reads indicating potential index issues
SELECT TOP 20
    qs.total_logical_reads,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_physical_reads,
    CAST(qs.total_physical_reads * 100.0 / qs.total_logical_reads AS DECIMAL(5,2)) AS physical_read_pct,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset 
          WHEN -1 THEN DATALENGTH(st.text) 
          ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.total_logical_reads > 1000
ORDER BY qs.total_logical_reads DESC;

-- Queries with memory grants and spills (SQL Server 2016+)
SELECT TOP 20
    qs.execution_count,
    qs.total_grant_kb / qs.execution_count AS avg_grant_kb,
    qs.total_used_grant_kb / qs.execution_count AS avg_used_grant_kb,
    qs.total_spills,
    CASE WHEN qs.total_spills > 0 THEN 'YES' ELSE 'NO' END AS has_spills,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset 
          WHEN -1 THEN DATALENGTH(st.text) 
          ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.total_grant_kb > 0
ORDER BY qs.total_spills DESC, qs.total_grant_kb DESC;

-- Query performance trends by creation and last execution time
SELECT 
    qs.creation_time,
    qs.last_execution_time,
    DATEDIFF(HOUR, qs.creation_time, GETDATE()) AS plan_age_hours,
    DATEDIFF(MINUTE, qs.last_execution_time, GETDATE()) AS minutes_since_last_exec,
    qs.execution_count,
    qs.total_elapsed_time / 1000 / qs.execution_count AS avg_duration_ms,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset 
          WHEN -1 THEN DATALENGTH(st.text) 
          ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.execution_count > 100
ORDER BY qs.total_elapsed_time DESC;

-- Parallel execution patterns with DOP analysis (SQL Server 2019+)
SELECT TOP 20
    qs.execution_count,
    qs.last_dop,
    qs.min_dop,
    qs.max_dop,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.total_elapsed_time / 1000 AS total_duration_ms,
    CASE WHEN qs.total_elapsed_time > 0 
         THEN CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(5,1)) 
         ELSE 0 END AS cpu_efficiency_pct,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset 
          WHEN -1 THEN DATALENGTH(st.text) 
          ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.max_dop > 1
ORDER BY qs.total_worker_time DESC;

Remove specific high-impact cached plans This script removes execution plans for queries consuming excessive resources, forcing recompilation with potentially better plans.

-- CAUTION: Test in development first
-- This removes plans for queries with high average CPU time
DECLARE @plan_handle varbinary(64);
DECLARE plan_cursor CURSOR FOR
    SELECT DISTINCT qs.plan_handle
    FROM sys.dm_exec_query_stats qs
    WHERE qs.total_worker_time / qs.execution_count > 1000000  -- 1 second avg CPU
    AND qs.execution_count > 10;

OPEN plan_cursor;
FETCH NEXT FROM plan_cursor INTO @plan_handle;

WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC FREEPROCCACHE(@plan_handle);
    FETCH NEXT FROM plan_cursor INTO @plan_handle;
END

CLOSE plan_cursor;
DEALLOCATE plan_cursor;

Expected Impact: Forces query recompilation which may generate better execution plans, but causes temporary performance degradation during recompilation.

Create monitoring baseline from current statistics This script captures current query performance metrics into a permanent table for trend analysis.

-- Create baseline table for query performance monitoring
IF OBJECT_ID('dbo.QueryStatsBaseline', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.QueryStatsBaseline (
        baseline_date datetime2 NOT NULL DEFAULT GETDATE(),
        sql_handle varbinary(64),
        plan_handle varbinary(64),
        statement_start_offset int,
        query_hash binary(8),
        query_plan_hash binary(8),
        execution_count bigint,
        total_worker_time bigint,
        total_elapsed_time bigint,
        total_logical_reads bigint,
        total_physical_reads bigint,
        total_spills bigint,
        query_text nvarchar(MAX)
    );
    
    CREATE CLUSTERED INDEX CIX_QueryStatsBaseline_Date 
    ON dbo.QueryStatsBaseline (baseline_date);
END

-- Insert current statistics
INSERT INTO dbo.QueryStatsBaseline (
    sql_handle, plan_handle, statement_start_offset, query_hash, query_plan_hash,
    execution_count, total_worker_time, total_elapsed_time, 
    total_logical_reads, total_physical_reads, total_spills, query_text
)
SELECT 
    qs.sql_handle, qs.plan_handle, qs.statement_start_offset, 
    qs.query_hash, qs.query_plan_hash,
    qs.execution_count, qs.total_worker_time, qs.total_elapsed_time,
    qs.total_logical_reads, qs.total_physical_reads, qs.total_spills,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset 
          WHEN -1 THEN DATALENGTH(st.text) 
          ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.execution_count > 5;

Expected Impact: Establishes performance baseline for identifying query regression and tracking optimization efforts over time.

Identify and address parameter sniffing issues This script finds queries with high execution count variations that suggest parameter sniffing problems.

-- Find potential parameter sniffing candidates
WITH PotentialSniffing AS (
    SELECT 
        qs.query_hash,
        COUNT(*) as plan_count,
        MIN(qs.execution_count) as min_executions,
        MAX(qs.execution_count) as max_executions,
        AVG(CAST(qs.total_worker_time AS FLOAT) / qs.execution_count) as avg_cpu_per_exec,
        STDEV(CAST(qs.total_worker_time AS FLOAT) / qs.execution_count) as cpu_stdev
    FROM sys.dm_exec_query_stats qs
    WHERE qs.execution_count > 10
    GROUP BY qs.query_hash
    HAVING COUNT(*) > 1  -- Multiple plans for same query
    AND (MAX(qs.execution_count) > MIN(qs.execution_count) * 10  -- Execution count variance
         OR STDEV(CAST(qs.total_worker_time AS FLOAT) / qs.execution_count) > 
            AVG(CAST(qs.total_worker_time AS FLOAT) / qs.execution_count) * 0.5)  -- Performance variance
)
SELECT 
    ps.query_hash,
    ps.plan_count,
    ps.min_executions,
    ps.max_executions,
    ps.avg_cpu_per_exec / 1000 as avg_cpu_ms,
    ps.cpu_stdev / 1000 as cpu_stdev_ms,
    st.text as sample_query_text
FROM PotentialSniffing ps
CROSS APPLY (
    SELECT TOP 1 qs2.sql_handle
    FROM sys.dm_exec_query_stats qs2
    WHERE qs2.query_hash = ps.query_hash
) qh
CROSS APPLY sys.dm_exec_sql_text(qh.sql_handle) st
ORDER BY ps.cpu_stdev DESC;

-- Use OPTION(RECOMPILE) or optimize for unknown for identified queries
PRINT 'Consider adding OPTION(RECOMPILE) or OPTIMIZE FOR UNKNOWN to queries above';

Expected Impact: Identifies queries suffering from parameter sniffing issues that need query hints or stored procedure modifications.

Implement automated monitoring using SQL Server Agent jobs that capture sys.dm_exec_query_stats data every 15 minutes into permanent tables. This preserves statistics across server restarts and plan cache flushes, enabling trend analysis and regression detection.

Configure appropriate max server memory to prevent excessive plan cache pressure that causes premature statistics loss. Size the procedure cache component based on your query workload, typically 10-20% of total buffer pool for OLTP systems with many ad-hoc queries.

Enable Query Store in SQL Server 2016+ as a complement to sys.dm_exec_query_stats. Query Store provides persistent query performance data with automatic plan regression detection, but still use this DMV for real-time analysis since Query Store has sampling limitations and flush intervals.

Establish performance baselines by regularly capturing top resource consumers from sys.dm_exec_query_stats. Create alerting thresholds based on standard deviations from baseline metrics rather than absolute values, as query performance varies significantly across different applications and time periods.

Implement proper indexing strategies based on logical reads patterns identified through this DMV. Queries consistently showing high logical_reads_per_execution values indicate missing or ineffective indexes that require analysis through execution plan examination and index usage statistics.

Set up monitoring for memory grant patterns using the total_grant_kb and total_spills columns. Frequent spills indicate insufficient memory grants or poor cardinality estimates requiring statistics updates, query rewriting, or Resource Governor memory grant adjustments.