sys.dm_exec_procedure_stats - Usage & Examples

SQL Server maintains execution statistics for stored procedures in memory as part of the plan cache structure. When a stored procedure executes, the Query Processor updates counters in the sys.dm_exec_procedure_stats DMV through the Plan Cache Manager component. These statistics accumulate from the first execution after the procedure enters the cache until the plan is evicted.

The statistics reset occurs when the Buffer Pool Manager removes cached plans due to memory pressure, when DBCC FREEPROCCACHE executes, during server restarts, or when ALTER PROCEDURE statements invalidate existing plans. SQL Server 2016 introduced Query Store as a persistent alternative to these volatile DMV statistics, addressing the data loss issue inherent in plan cache-dependent views.

The Lock Manager and Scheduler components contribute wait time statistics, while the Buffer Pool Manager provides logical and physical read metrics. CPU time measurements come directly from the Windows performance counters at the thread level during procedure execution.

In SQL Server 2019 and later, Intelligent Query Processing features like memory grant feedback can cause plan recompilation, which resets individual procedure statistics while preserving aggregate data. SQL Server 2022 enhanced the DMV with parameter sniffing and plan forcing integration, providing more granular tracking of execution variations.

-- Identify top CPU consuming procedures
SELECT TOP 20
    DB_NAME(database_id) AS database_name,
    OBJECT_SCHEMA_NAME(object_id, database_id) AS schema_name,
    OBJECT_NAME(object_id, database_id) AS procedure_name,
    execution_count,
    total_worker_time / 1000 AS total_cpu_ms,
    (total_worker_time / execution_count) / 1000 AS avg_cpu_ms,
    total_logical_reads,
    total_logical_reads / execution_count AS avg_logical_reads
FROM sys.dm_exec_procedure_stats
WHERE database_id > 4 -- Exclude system databases
ORDER BY total_worker_time DESC;

-- Find procedures with highest physical I/O impact
SELECT TOP 20
    DB_NAME(database_id) AS database_name,
    OBJECT_NAME(object_id, database_id) AS procedure_name,
    execution_count,
    total_physical_reads,
    total_physical_reads / execution_count AS avg_physical_reads,
    total_logical_reads / execution_count AS avg_logical_reads,
    (total_physical_reads * 100.0) / NULLIF(total_logical_reads, 0) AS physical_read_pct
FROM sys.dm_exec_procedure_stats
WHERE total_physical_reads > 0
    AND database_id > 4
ORDER BY total_physical_reads DESC;

-- Detect procedures with performance degradation patterns
SELECT 
    DB_NAME(database_id) AS database_name,
    OBJECT_NAME(object_id, database_id) AS procedure_name,
    execution_count,
    min_worker_time / 1000 AS min_cpu_ms,
    max_worker_time / 1000 AS max_cpu_ms,
    (max_worker_time - min_worker_time) / 1000 AS cpu_variation_ms,
    min_logical_reads,
    max_logical_reads,
    max_logical_reads - min_logical_reads AS read_variation,
    cached_time,
    last_execution_time
FROM sys.dm_exec_procedure_stats
WHERE max_worker_time > min_worker_time * 10 -- 10x variation
    AND execution_count > 10
    AND database_id > 4
ORDER BY (max_worker_time - min_worker_time) DESC;

-- Identify infrequently used procedures consuming cache memory
SELECT 
    DB_NAME(database_id) AS database_name,
    OBJECT_NAME(object_id, database_id) AS procedure_name,
    execution_count,
    cached_time,
    last_execution_time,
    DATEDIFF(day, last_execution_time, GETDATE()) AS days_since_last_execution,
    total_worker_time / 1000 AS total_cpu_ms,
    total_logical_reads
FROM sys.dm_exec_procedure_stats
WHERE DATEDIFF(day, last_execution_time, GETDATE()) > 30
    AND database_id > 4
ORDER BY days_since_last_execution DESC, total_worker_time DESC;

-- Correlate procedure stats with current plan cache size
SELECT 
    COUNT(*) AS cached_procedures,
    SUM(execution_count) AS total_executions,
    AVG(total_worker_time / execution_count) / 1000 AS avg_cpu_per_execution_ms,
    MIN(cached_time) AS oldest_cached_plan,
    MAX(last_execution_time) AS most_recent_execution
FROM sys.dm_exec_procedure_stats
WHERE database_id > 4;

Clear plan cache for specific problematic procedures This removes cached plans for procedures showing performance degradation, forcing recompilation with current statistics.

-- Replace 'YourDatabase', 'dbo', 'ProblemProcedure' with actual values
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'DBCC FREEPROCCACHE';

-- For SQL Server 2016+, target specific procedures
DECLARE @plan_handle VARBINARY(64);
SELECT @plan_handle = cp.plan_handle
FROM sys.dm_exec_procedure_stats ps
INNER JOIN sys.dm_exec_cached_plans cp ON ps.plan_handle = cp.plan_handle
WHERE ps.database_id = DB_ID('YourDatabase')
    AND OBJECT_NAME(ps.object_id, ps.database_id) = 'ProblemProcedure'
    AND OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) = 'dbo';

IF @plan_handle IS NOT NULL
BEGIN
    DBCC FREEPROCCACHE(@plan_handle);
    PRINT 'Cleared plan cache for specific procedure';
END

Create monitoring baseline for procedure performance Establishes historical tracking to identify performance regressions over time.

-- Create baseline table for procedure performance monitoring
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'procedure_performance_baseline')
BEGIN
    CREATE TABLE dbo.procedure_performance_baseline (
        capture_date DATETIME2 DEFAULT SYSDATETIME(),
        database_name SYSNAME,
        schema_name SYSNAME,
        procedure_name SYSNAME,
        execution_count BIGINT,
        avg_cpu_ms BIGINT,
        avg_logical_reads BIGINT,
        avg_physical_reads BIGINT,
        avg_duration_ms BIGINT,
        PRIMARY KEY (capture_date, database_name, schema_name, procedure_name)
    );
END

-- Insert current baseline data
INSERT INTO dbo.procedure_performance_baseline 
    (database_name, schema_name, procedure_name, execution_count, 
     avg_cpu_ms, avg_logical_reads, avg_physical_reads, avg_duration_ms)
SELECT 
    DB_NAME(database_id),
    OBJECT_SCHEMA_NAME(object_id, database_id),
    OBJECT_NAME(object_id, database_id),
    execution_count,
    (total_worker_time / execution_count) / 1000,
    total_logical_reads / execution_count,
    total_physical_reads / execution_count,
    (total_elapsed_time / execution_count) / 1000
FROM sys.dm_exec_procedure_stats
WHERE database_id > 4 AND execution_count > 0;

PRINT 'Baseline captured for ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' procedures';

Implement procedure execution alerts for resource-intensive operations Creates alerts when procedures exceed performance thresholds, enabling proactive intervention.

-- Create alert for high-resource procedures
-- Test thoroughly in development before production deployment
CREATE OR ALTER PROCEDURE dbo.sp_monitor_procedure_performance
    @cpu_threshold_ms INT = 5000,
    @logical_read_threshold INT = 100000,
    @execution_count_threshold INT = 100
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @alert_message NVARCHAR(MAX) = '';
    
    SELECT 
        @alert_message = @alert_message + 
        'ALERT: ' + DB_NAME(database_id) + '.' + 
        OBJECT_SCHEMA_NAME(object_id, database_id) + '.' + 
        OBJECT_NAME(object_id, database_id) + 
        ' - Avg CPU: ' + CAST((total_worker_time / execution_count) / 1000 AS VARCHAR(20)) + 'ms' +
        ' - Avg Reads: ' + CAST(total_logical_reads / execution_count AS VARCHAR(20)) + CHAR(13) + CHAR(10)
    FROM sys.dm_exec_procedure_stats
    WHERE database_id > 4
        AND execution_count >= @execution_count_threshold
        AND (
            (total_worker_time / execution_count) / 1000 > @cpu_threshold_ms
            OR total_logical_reads / execution_count > @logical_read_threshold
        );
    
    IF LEN(@alert_message) > 0
    BEGIN
        RAISERROR(@alert_message, 16, 1) WITH LOG;
    END
END

Configure Query Store in SQL Server 2016+ to maintain persistent procedure performance history that survives plan cache evictions. Set Query Store to READ_WRITE mode with adequate size allocation (minimum 1GB for production systems) and enable automatic cleanup of stale data.

Implement regular statistics updates using maintenance plans or Ola Hallengren scripts to prevent parameter sniffing issues that cause procedure performance variations. Schedule UPDATE STATISTICS operations during maintenance windows, focusing on tables with high modification rates.

Monitor plan cache hit ratios and memory pressure indicators through sys.dm_os_memory_clerks and sys.dm_os_ring_buffers. Configure max server memory appropriately to prevent excessive plan cache evictions that reset procedure statistics.

Establish automated baseline collection of sys.dm_exec_procedure_stats data every 4 hours to track performance trends before plan cache resets occur. Use SQL Server Agent jobs to capture and store historical performance data in dedicated monitoring tables.

Configure Database Mail and SQL Server Agent alerts to notify when procedures exceed CPU time or logical read thresholds. Set reasonable baselines based on your application's normal operating patterns, typically 2-3 standard deviations above historical averages.

Avoid frequent DBCC FREEPROCCACHE operations in production environments as they reset all accumulated procedure statistics. Instead, target specific problem procedures using plan handles when recompilation is necessary.