Profile Costly Cached Procs with sys.dm_exec_procedure_stats

calendar Sep 18, 2026 / · 9 min read · sql server sys.dm_exec_procedure_stats sys.dm_exec_query_stats sys.dm_exec_sql_text sys.dm_exec_text_query_plan sys.objects sys.procedures sys.dm_pdw_nodes_exec_procedure_stats stored procedures procedure cache cpu time performance tuning scripts  ·
Share on: linkedin copy

A DMV query that grinds to five or ten minutes against a large plan cache is a real failure mode, not a hypothetical one — joining sys.dm_exec_query_stats to sys.dm_exec_sql_text row by row scales badly once the cache holds enough plans. sys.dm_exec_procedure_stats exists to sidestep exactly that cost: one row per cached stored procedure, already rolled up by execution count, CPU time, reads, and elapsed time, with an object_id column that resolves to a name without touching the slow per-statement text function at all.

Purpose and Overview

sys.dm_exec_procedure_stats returns one row for every cached stored procedure plan, covering SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, Analytics Platform System (PDW) — under the name sys.dm_pdw_nodes_exec_procedure_stats there, and unsupported against serverless SQL pool — and SQL database in Microsoft Fabric. Each row identifies the procedure by database_id, object_id, and a type code (P for a SQL stored procedure, PC for an assembly/CLR stored procedure, X for an extended stored procedure, each with a matching type_desc), then carries sql_handle and plan_handle for correlating to other DMVs, cached_time, last_execution_time, and execution_count. Around those sit four parallel families of counters — total_, last_, min_, and max_ — for worker_time (CPU, in microseconds), physical_reads, logical_writes, logical_reads, and elapsed_time, plus total_spills/last_spills/min_spills/max_spills tracking pages spilled to tempdb, available starting with SQL Server 2017 (14.x) CU3.

The row's lifetime is tied directly to the plan's presence in cache: SQL Server starts collecting these statistics the moment a procedure's plan is placed in the procedure cache and keeps updating them on every completed execution for as long as that plan stays there. Once the plan is evicted — memory pressure, a schema change, or an explicit recompile — the row disappears along with it, and that removal raises a query_cache_removal_statistics event, the same mechanism used by sys.dm_exec_query_stats. Running SP_RECOMPILE against a procedure removes its cached plan and, with it, every statistic this view had accumulated — worth remembering before reaching for a recompile as a first troubleshooting step against a procedure that's actually the one under investigation.

The view doesn't identify a procedure by name on its own — only object_id and database_id — so every query against it needs either OBJECT_NAME(object_id, database_id) or a join to a catalog view that carries the name. The ranking query below also filters out database_id = 32767: that's the Resource database, a hidden, read-only database that physically stores every SQL Server system object (sys.objects and the rest of the sys schema) without holding any user data or user metadata of its own — its ID sits at the top of the 32,767 databases an instance can hold, reserved specifically so it never collides with a real one.

Code Breakdown

Three passes make up this script: a procedure-level ranking that excludes the Resource database and computes true per-execution averages, a name-and-modification-date resolution against sys.objects, and a statement-level drill-down into sys.dm_exec_query_stats for the one procedure that needs a closer look.

 1SELECT TOP 25
 2    OBJECT_NAME(d.object_id, d.database_id)   AS procedure_name,
 3    DB_NAME(d.database_id)                    AS database_name,
 4    d.cached_time,
 5    d.last_execution_time,
 6    d.execution_count,
 7    d.total_worker_time,
 8    d.total_worker_time / d.execution_count   AS avg_worker_time,
 9    d.total_elapsed_time,
10    d.total_elapsed_time / d.execution_count  AS avg_elapsed_time,
11    d.total_logical_reads,
12    d.total_logical_reads / d.execution_count AS avg_logical_reads,
13    d.total_physical_reads
14FROM sys.dm_exec_procedure_stats AS d
15WHERE d.database_id <> 32767
16ORDER BY d.total_worker_time DESC;

Ranking by total_worker_time and filtering out the Resource database

Ordering by total_worker_time rather than total_elapsed_time isolates procedures that are genuinely CPU-expensive from ones that are merely slow because they're waiting on locks, I/O, or a blocked resource — elapsed time includes all of that waiting, worker time does not. Dividing each total_ column by execution_count turns a cumulative-since-cached figure into a true per-call average, which is what actually separates a procedure that runs a thousand times a day at low cost from one that ran once and happened to be expensive. The database_id <> 32767 filter keeps the Resource database's internal objects out of the result entirely, since nothing in it is a candidate for tuning.

 1SELECT
 2    SCHEMA_NAME(o.schema_id)   AS schema_name,
 3    OBJECT_NAME(ps.object_id)  AS procedure_name,
 4    o.create_date,
 5    o.modify_date,
 6    ps.cached_time,
 7    ps.last_execution_time,
 8    ps.execution_count
 9FROM sys.dm_exec_procedure_stats AS ps
10INNER JOIN sys.objects AS o
11    ON o.object_id = ps.object_id
12WHERE o.type = 'P'
13ORDER BY ps.total_worker_time DESC;

Resolving object_id to a name and a modification date with sys.objects

OBJECT_NAME(object_id, database_id) is the right call when a query spans multiple databases in one result set, but a direct join to sys.objects filtered to type = 'P' is lighter when the query is already scoped to a single database — and it adds two columns sys.dm_exec_procedure_stats doesn't carry at all: create_date and modify_date. sys.procedures is an equally valid alternative here, narrowed specifically to stored procedures rather than every object type, and returns the same create_date/modify_date pair.

 1SELECT
 2    CAST(qp.query_plan AS XML) AS statement_plan,
 3    SUBSTRING(
 4        st.text,
 5        qs.statement_start_offset / 2 + 1,
 6        (
 7            (CASE WHEN qs.statement_end_offset = -1
 8                THEN DATALENGTH(st.text)
 9                ELSE qs.statement_end_offset
10            END) - qs.statement_start_offset
11        ) / 2 + 1
12    )                            AS statement_text,
13    qs.execution_count,
14    qs.total_worker_time,
15    qs.total_worker_time / qs.execution_count  AS avg_worker_time,
16    qs.total_elapsed_time,
17    qs.total_logical_reads
18FROM sys.dm_exec_query_stats AS qs
19INNER JOIN sys.dm_exec_procedure_stats AS ps
20    ON qs.sql_handle = ps.sql_handle
21CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
22CROSS APPLY sys.dm_exec_text_query_plan(
23    qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset
24) AS qp
25WHERE ps.object_id = OBJECT_ID(N'dbo.YourProcedureName')
26ORDER BY qs.total_worker_time DESC;

Drilling into the worst statement with sys.dm_exec_query_stats

sys.dm_exec_procedure_stats groups every statement inside a procedure under one row — a twelve-statement procedure with eleven fast statements and one expensive one still reports as a single aggregated cost, with no way to see which line is responsible. The sql_handle value on this view lines up directly with matching rows in sys.dm_exec_query_stats for statements executed from inside that same stored procedure, which is the join the query above relies on to isolate the one line actually driving the cost. Joining on the shared sql_handle pulls every individually compiled statement belonging to the flagged procedure; CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) returns the full batch text, and the SUBSTRING expression against statement_start_offset and statement_end_offset carves out just the one statement that row's stats belong to — dividing by 2 because those offsets are counted in bytes against an nvarchar string, and handling statement_end_offset = -1 (meaning "runs to the end of the batch") with DATALENGTH(st.text). CROSS APPLY sys.dm_exec_text_query_plan(...) then returns that single statement's own XML execution plan, rather than the plan for the procedure as a whole.

Key Benefits and Use Cases

  • Cheap first pass — one row per cached procedure with no per-statement text lookup, so it stays fast even against a plan cache large enough to make a raw sys.dm_exec_query_stats scan slow.
  • Separates CPU cost from wait time — ranking on total_worker_time instead of total_elapsed_time points at procedures actually burning CPU, not ones merely stuck waiting.
  • min/max alongside total — a procedure with one catastrophic outlier execution looks different from one that's uniformly expensive on every call, and the paired min/max columns show which.
  • No setup required — the view returns data immediately against a stock instance; no Query Store, Extended Events session, or trace flag has to be enabled first.
  • A direct path to the offending line — the sql_handle correlation to sys.dm_exec_query_stats is a documented mechanism for going from a flagged procedure to its individual statements, not an unsupported workaround.
  • A CPU-weighted score worth borrowing — ranking by total_worker_time alone works, but weighting it against reads and writes as log((TotalCPUTime × 3) + TotalLogicalReads + TotalLogicalWrites) gives CPU roughly three times the pull of either I/O count when prioritizing several flagged procedures against each other, a useful blend even without a dedicated monitoring tool.

Performance Considerations

  • Memory-only, no history: a row exists only as long as its plan stays cached; eviction removes the row and its entire accumulated history along with it, with nothing retained for later review.
  • SP_RECOMPILE clears the baseline: forcing a recompile to "fix" a slow procedure also wipes the very statistics that were being used to diagnose it.
  • Permission floor varies by version: SQL Server and SQL Managed Instance require VIEW SERVER STATE; SQL Server 2022 and later narrow that to VIEW SERVER PERFORMANCE STATE. On Azure SQL Database, Basic/S0/S1 tiers and elastic pools need the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## role; other Azure SQL Database tiers need VIEW DATABASE STATE on the database or the same server role.
  • Natively compiled procedures behave differently: querying a memory-optimized table always reports 0 for physical reads, logical writes, and logical reads, and plan_handle reads 0x000; worker time for executions under a millisecond can also read 0 unless statistics collection is explicitly enabled for that procedure.
  • Results reflect finished executions only: figures can vary between two consecutive runs of the same query, because in-flight executions that haven't completed yet aren't represented.

Practical Tips

  • Filter out database_id = 32767 on every ranking query against this view — the Resource database's own system objects add nothing to a tuning exercise.
  • Log results to a history table on a schedule, since nothing here survives a plan eviction — a manual GETDATE()-stamped insert at the end of a procedure is the only way to get a permanent execution log independent of the cache.
  • Cross-check modify_date from sys.objects (or sys.procedures) against cached_time before acting on a flagged procedure — one altered after its current stats started accumulating may not yet reflect a recent index or logic change.
  • Don't stop at the procedure-level row once something is flagged — join into sys.dm_exec_query_stats and sys.dm_exec_sql_text to find which individual statement inside a multi-statement procedure is actually driving the total.
  • If a direct sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text scan runs slow against a large cache, pull the sql_handle from sys.dm_exec_procedure_stats first and filter on it directly rather than scanning the whole query-stats view unfiltered.

Conclusion

sys.dm_exec_procedure_stats turns "which stored procedure is expensive" into a cheap, one-row-per-procedure ranking, and its sql_handle correlation to sys.dm_exec_query_stats extends the same query into the exact statement responsible once a procedure is flagged. Run as a scheduled check with results logged to a table, it's a lightweight first-pass alternative to Query Store for any instance where that isn't running yet — the starting point for deciding which procedure earns a closer look before ever opening an execution plan.

References

Posts in this series