sys.dm_exec_sql_text - Usage & Examples

sys.dm_exec_sql_text operates as a table-valued function that extracts SQL statement text from the plan cache using the sql_handle as a key. When SQL Server compiles a query plan, it stores both the execution plan and the associated SQL text in the plan cache buffer pool regions. The sql_handle serves as a hash-based identifier that uniquely identifies the SQL batch text.

The DMV accesses the cached query text directly from memory structures within the buffer pool. In SQL Server 2016 and later, Query Store integration provides additional context, but sys.dm_exec_sql_text remains the primary mechanism for retrieving dynamic SQL text from active sessions and cached plans. The DMV returns the complete batch text, not just individual statements within a batch.

SQL Server 2019 introduced UTF-8 support which affects text encoding in the results. SQL Server 2022 enhanced memory management for plan cache retention, improving the reliability of text retrieval for longer-running queries. The sql_handle remains valid until the plan is evicted from cache due to memory pressure, plan aging, or explicit cache clearing.

When a plan is parameterized, the DMV returns the parameterized version of the query text, not the original literal values. This behavior is consistent across all versions but affects troubleshooting dynamic SQL performance issues where parameter sniffing occurs.

-- Get SQL text for currently executing requests
SELECT 
    s.session_id,
    r.request_id,
    r.status,
    r.command,
    st.text AS sql_text,
    r.cpu_time,
    r.logical_reads,
    r.writes
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE s.is_user_process = 1;

-- Find most resource-intensive cached queries with their SQL text
SELECT TOP 20
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    st.text AS sql_text,
    qs.creation_time,
    qs.last_execution_time
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;

-- Identify queries with specific patterns or table references
SELECT DISTINCT
    st.text AS sql_text,
    qs.execution_count,
    qs.total_elapsed_time,
    qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%YourTableName%'
    AND st.text NOT LIKE '%sys.dm_exec_sql_text%'
ORDER BY qs.total_elapsed_time DESC;

-- Get SQL text for blocking sessions
SELECT 
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocking_text.text AS blocking_sql,
    blocked_text.text AS blocked_sql,
    w.wait_type,
    w.wait_time_ms
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_text
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
LEFT JOIN sys.dm_os_waiting_tasks w ON blocked.session_id = w.session_id
WHERE blocked.blocking_session_id > 0;

-- Find SQL text for plans consuming excessive memory grants
SELECT 
    r.session_id,
    r.request_id,
    r.granted_query_memory * 8 AS granted_memory_kb,
    r.used_memory_grant * 8 AS used_memory_kb,
    st.text AS sql_text,
    r.status,
    r.start_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.granted_query_memory > 1000 -- More than 8MB
ORDER BY r.granted_query_memory DESC;

Clear specific problematic queries from plan cache Removes cached plans for queries identified through sys.dm_exec_sql_text analysis. Use when parameter sniffing or outdated statistics cause poor plan selection.

-- Remove specific query plans from cache
-- WARNING: Test in development first, impacts all sessions using these plans
DECLARE @sql_handle varbinary(64);

-- Get the sql_handle for the problematic query
SELECT @sql_handle = qs.sql_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%YourSpecificQuery%'
    AND st.text NOT LIKE '%sys.dm_exec_sql_text%';

-- Remove the plan from cache
IF @sql_handle IS NOT NULL
    DBCC FREEPROCCACHE(@sql_handle);

Create monitoring query for resource-intensive statements Establishes ongoing monitoring for queries consuming excessive resources, identified through sys.dm_exec_sql_text analysis.

-- Create monitoring table for resource-intensive queries
-- Run this periodically to track query performance trends
IF OBJECT_ID('dbo.QueryPerformanceBaseline', 'U') IS NOT NULL
    DROP TABLE dbo.QueryPerformanceBaseline;

CREATE TABLE dbo.QueryPerformanceBaseline (
    capture_time datetime2 DEFAULT GETDATE(),
    sql_handle varbinary(64),
    sql_text nvarchar(max),
    execution_count bigint,
    total_worker_time bigint,
    total_elapsed_time bigint,
    total_logical_reads bigint,
    avg_cpu_time AS (total_worker_time / execution_count),
    avg_elapsed_time AS (total_elapsed_time / execution_count),
    avg_logical_reads AS (total_logical_reads / execution_count)
);

-- Populate baseline data
INSERT INTO dbo.QueryPerformanceBaseline (
    sql_handle, sql_text, execution_count, 
    total_worker_time, total_elapsed_time, total_logical_reads
)
SELECT 
    qs.sql_handle,
    st.text,
    qs.execution_count,
    qs.total_worker_time,
    qs.total_elapsed_time,
    qs.total_logical_reads
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.total_worker_time > 100000 -- Focus on high CPU queries
    OR qs.total_logical_reads > 1000000; -- Focus on high I/O queries

Automated plan cache analysis script Creates a comprehensive analysis of plan cache contents using sys.dm_exec_sql_text for proactive performance management.

-- Comprehensive plan cache analysis using sys.dm_exec_sql_text
-- Run weekly to identify performance trends and optimization opportunities
WITH QueryStats AS (
    SELECT 
        qs.sql_handle,
        st.text AS sql_text,
        qs.execution_count,
        qs.total_worker_time,
        qs.total_elapsed_time,
        qs.total_logical_reads,
        qs.total_physical_reads,
        qs.creation_time,
        qs.last_execution_time,
        -- Calculate averages
        qs.total_worker_time / qs.execution_count AS avg_cpu_time,
        qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
        qs.total_logical_reads / qs.execution_count AS avg_logical_reads
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    WHERE qs.execution_count > 10 -- Filter noise
)
SELECT 
    'High CPU Usage' AS issue_type,
    sql_text,
    execution_count,
    avg_cpu_time,
    total_worker_time,
    last_execution_time
FROM QueryStats
WHERE avg_cpu_time > 1000000 -- 1 second average CPU

UNION ALL

SELECT 
    'High Logical Reads' AS issue_type,
    sql_text,
    execution_count,
    avg_logical_reads,
    total_logical_reads,
    last_execution_time
FROM QueryStats
WHERE avg_logical_reads > 10000 -- 10k page reads average

ORDER BY issue_type, total_worker_time DESC, total_logical_reads DESC;

Configure Query Store on all user databases to complement sys.dm_exec_sql_text analysis with historical query performance data. Set Query Store to capture mode AUTO with a retention period of at least 30 days for trend analysis.

Implement automated monitoring that regularly queries sys.dm_exec_query_stats joined with sys.dm_exec_sql_text to identify performance regressions. Create alerts for queries exceeding CPU time thresholds or logical read baselines established during performance testing.

Establish plan cache sizing appropriately using max server memory settings to prevent excessive plan eviction. Monitor plan cache hit ratios and adjust memory allocation when plan cache pressure causes frequent recompilation of expensive queries identified through sys.dm_exec_sql_text analysis.

Configure extended events sessions to capture query execution details for queries that cannot be analyzed through sys.dm_exec_sql_text due to plan cache eviction. Create lightweight sessions targeting high-duration or high-CPU queries to maintain query text visibility beyond cache retention limits.

Use forced parameterization or plan guides for frequently executed ad-hoc queries identified through sys.dm_exec_sql_text to reduce plan cache pollution and improve cache efficiency. This prevents plan cache bloat that reduces the effectiveness of performance analysis through DMV queries.