sys.dm_exec_query_plan - Usage & Examples

The query plan cache stores compiled execution plans in memory to avoid recompilation overhead. When SQL Server compiles a T-SQL statement, the Query Optimizer generates an execution plan that gets stored in the plan cache (part of the buffer pool). sys.dm_exec_query_plan retrieves these cached plans by joining with sys.dm_exec_cached_plans or sys.dm_exec_requests.

The plan cache operates using hash tables where plan handles serve as unique identifiers. Plans remain cached until memory pressure forces eviction, the database is taken offline, or DBCC FREEPROCCACHE removes them. SQL Server 2016 introduced Query Store which provides persistent plan storage, but sys.dm_exec_query_plan still shows currently cached plans.

In SQL Server 2019+, the DMV includes support for adaptive query processing features like adaptive joins and memory grant feedback. SQL Server 2022 added parameter sensitive plan optimization details in the XML output. The XML format follows the Showplan schema and includes operator costs, estimated rows, actual execution statistics (when available), and optimizer decisions.

Memory grants, parallelism decisions, index usage, and join algorithms are all visible in the plan XML. The RelOp elements contain the physical operators, while RunTimeInformation nodes (when present) show actual execution metrics. Plan compilation happens on the scheduler thread, and the resulting plan gets stored in the procedure cache hash table.

-- Find most expensive queries by average CPU time with their execution plans
SELECT TOP 20
    qs.sql_handle,
    qs.plan_handle,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qp.query_plan,
    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 statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY avg_cpu_time DESC;

-- Find plans with missing index recommendations
SELECT 
    qp.query_plan,
    cp.usecounts,
    cp.objtype,
    st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE qp.query_plan.exist('declare namespace qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
    //qplan:MissingIndexes') = 1
ORDER BY cp.usecounts DESC;

-- Currently executing queries with live execution plans
SELECT 
    r.session_id,
    r.request_id,
    r.start_time,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    qp.query_plan,
    st.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.session_id > 50
ORDER BY r.total_elapsed_time DESC;

-- Plans with high compile time or parallelism issues
SELECT 
    qs.plan_handle,
    qs.execution_count,
    qs.total_worker_time,
    qs.max_dop,
    cp.size_in_bytes,
    qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        (//p:StmtSimple/@StatementOptmEarlyAbortReason)[1]', 'varchar(50)') AS early_abort_reason,
    qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        (//p:StmtSimple/@StatementOptmLevel)[1]', 'varchar(50)') AS optimization_level
FROM sys.dm_exec_query_stats qs
INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.max_dop > 1
ORDER BY qs.total_worker_time DESC;

-- Parameter sensitive plans and compilation details (SQL Server 2022+)
-- PSP optimization details appear under the Dispatcher element in showplan XML
SELECT 
    qp.query_plan,
    qs.execution_count,
    qs.last_execution_time,
    qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        count(//p:Dispatcher)', 'int') AS psp_dispatchers,
    st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan.exist('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
    //p:Dispatcher') = 1;

Clear problematic plans from cache Removes specific bad plans from the procedure cache when they're causing performance issues.

-- Remove specific plan from cache (replace with actual plan_handle)
-- Test impact first - this forces recompilation
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

-- Alternative: Remove all plans for a specific database
-- USE WITH EXTREME CAUTION - forces all plans to recompile
-- DECLARE @db_id INT = DB_ID('YourDatabaseName');
-- DBCC FLUSHPROCINDB(@db_id);

Force plan recompilation for parameterized queries Forces recompilation of plans that may have parameter sniffing issues.

-- Add RECOMPILE hint to problem queries during investigation
-- Example query modification:
SELECT CustomerID, OrderDate, TotalAmount 
FROM Orders 
WHERE CustomerID = @CustomerId
OPTION (RECOMPILE);

-- For stored procedures with parameter sniffing issues:
-- Add OPTIMIZE FOR UNKNOWN hint
SELECT CustomerID, OrderDate, TotalAmount 
FROM Orders 
WHERE CustomerID = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId UNKNOWN));

Create plan guides for consistent execution plans Locks execution plans for critical queries without modifying application code.

-- Create plan guide to force specific plan behavior
-- Replace with actual query text and desired options
EXEC sp_create_plan_guide 
    @name = 'Guide_OrderLookup',
    @stmt = 'SELECT CustomerID, OrderDate FROM Orders WHERE CustomerID = @p1',
    @type = 'TEMPLATE',
    @module_or_batch = NULL,
    @params = '@p1 INT',
    @hints = 'OPTION (PARAMETERIZATION FORCED, OPTIMIZE FOR (@p1 = 12345))';

-- Verify plan guide creation
SELECT * FROM sys.plan_guides WHERE name = 'Guide_OrderLookup';

Enable Query Store on SQL Server 2016+ to capture plan history and performance regression detection. Query Store provides automatic plan correction and forced plan capabilities that complement sys.dm_exec_query_plan analysis.

Configure appropriate MAXDOP values based on CPU core count and workload characteristics. Use Resource Governor to control parallelism for specific workloads. Monitor the "cost threshold for parallelism" setting, typically raising it from the default value of 5 to 25-50 for OLTP workloads.

Implement regular plan cache analysis through automated scripts that identify plans with high CPU consumption, excessive logical reads, or missing index recommendations. Use SQL Server Agent jobs to capture and archive plan data during peak business hours.

Address parameter sniffing systematically by identifying queries with high execution count variations and plan instability. Consider OPTIMIZE FOR hints, plan guides, or query rewriting for problematic parameterized queries. SQL Server 2022's parameter sensitive plan optimization reduces many traditional parameter sniffing issues automatically.

Monitor plan cache hit ratios and memory pressure indicators. Size the buffer pool appropriately to maintain adequate plan cache space. Track plan evictions through sys.dm_os_performance_counters to identify memory pressure causing excessive recompilations.