Sqlprocstats Review

SQL Server Procedure Stats Review Skill

Purpose

Analyze runtime statistics collected from sys.dm_exec_procedure_stats, sys.dm_exec_trigger_stats, and sys.dm_exec_function_stats into the collect.proc_stats table. Applies 25 checks (R1–R25) across five categories:

• R1–R5 — Top resource consumers: identify which procedure, trigger, or function is

burning the most CPU, reads, or elapsed time in the collection interval

• R6–R10 — Per-execution efficiency: flag objects that are expensive per call regardless

of how often they run — high average CPU, high reads, parameter sniffing signals, spills

• R11–R15 — Pattern detection: N+1 callers, chatty high-frequency procs, plan instability,

workload concentration, infrequent-but-heavy outliers

• R16–R20 — Trend analysis: worsening CPU/reads across snapshots, execution spikes,

plan changes, new high-cost entries (requires ≥ 3 snapshots from Q5)

• R21–R25 — Advanced runtime patterns: natively compiled proc regression, high CLR ratio,

trigger-dominated elapsed time, parallel-to-serial regression, Query Store plan instability

Input

Accept any of:

• Q1 output (Top CPU) — paste the result grid from 04_report_queries.sql Query 1
• Q2 output (Top Reads) — paste Query 2 result grid
• Q3 output (Top Callers) — paste Query 3 result grid
• Q4 output (Per-Execution Averages) — paste Query 4 result grid
• Q5 output (Trend / Time Series) — paste Query 5 result grid (requires ≥ 3 snapshots)
• Combined paste — paste two or more query outputs together; apply all applicable checks
• Natural language description — describe the metrics you see ("usp_GetOrders uses 80% of CPU")
• Statement-level stats — paste output from collect.query_stats using the report query in

scripts/collection/12_report_all_collections.sql Section 2; all R-checks apply equally to statement-level data — just note the object_name may be NULL for ad-hoc SQL

For trend checks (R16–R20), Q5 output with ≥ 3 rows per object is required. State "Cannot evaluate R16–R20 — trend data not provided" if Q5 is absent.

Column Reference

| Column | Source | Notes | |--------|--------|-------| | execs_in_interval | execution_count_delta | Executions since last snapshot | | cpu_ms_per_sec | worker_time_per_sec | CPU ms consumed per second of sample | | avg_cpu_ms | avg_worker_time_ms | Avg CPU per execution (ms) | | avg_elapsed_ms | avg_elapsed_time_ms | Avg wall-clock per execution (ms) | | max_cpu_ms | max_worker_time / 1000 | Single worst execution CPU (ms) | | avg_logical_reads | computed | Avg 8-KB page reads per execution | | reads_per_sec | computed | Logical reads per second | | avg_spills | computed | Avg TempDb spill pages per execution | | physical_reads_delta | delta | Total physical reads in interval | | physical_pct | computed | Physical reads as % of logical (cache miss rate) | | execs_per_sec | computed | Execution rate per second | | max_to_avg_cpu_ratio | computed | maxcpums / avgcpums — parameter sniffing signal | | cpu_to_elapsed_ratio | computed | avgcpu / avgelapsed — > 1.5 = parallel; 50% | > 80% | | avg_cpu_ms per execution | — | ≥ 1,000 ms | ≥ 10,000 ms | | avg_logical_reads per execution | — | ≥ 50,000 | ≥ 500,000 | | Physical reads as % of logical | — | > 10% | > 50% | | execs_in_interval | — | ≥ 10,000 | — | | execs_per_sec (chatty) | — | ≥ 10/s | ≥ 100/s | | avg_spills per execution | — | ≥ 1 | ≥ 10 | | cpu_to_elapsed_ratio (parallel waste) | — | > 1.5 | > 3.0 | | cpu_to_elapsed_ratio (blocking/IO wait) | — | 2× mean | > 5× mean | | Trend: CPU worsening (monotonic) | 2 snapshots | 3+ snapshots | — |

---

Statement-Level Checks (R1–R5): Top Resource Consumers

Run these first. They identify which objects dominate the workload in the collection interval.

R1 — CPU Hotspot

• Trigger: cpu_ms_per_sec ≥ 50 for a single object, OR that object's total_worker_time_delta represents > 50% of the sum across all objects in the result set
• Severity: Warning if ≥ 50 ms/s or > 50%; Critical if ≥ 500 ms/s or > 80%
• Fix: Run /sqlplan-review on this procedure's cached plan to identify the expensive operator. Check for missing indexes, implicit conversions, or missing parallelism. Use OPTION (RECOMPILE) as immediate mitigation if parameter sniffing is suspected (see R9).

R2 — Read Hotspot

• Trigger: reads_per_sec ≥ 5,000 for a single object, OR that object's total_logical_reads_delta represents > 50% of total reads in the result
• Severity: Warning if ≥ 5,000 reads/s or > 50%; Critical if ≥ 50,000 reads/s or > 80%
• Fix: The object reads disproportionately from the buffer pool. Run /sqlindex-advisor on its execution plan. High avg_logical_reads (see R7) indicates a per-execution index problem; high reads_per_sec with low avg_logical_reads indicates high frequency (see R4/R12).

R3 — Duration Hotspot

• Trigger: avg_elapsed_ms ≥ 5,000 ms AND execs_in_interval > 0
• Severity: Warning if ≥ 5,000 ms; Critical if ≥ 30,000 ms
• Fix: Objects with high elapsed time are holding connections and blocking dependent code. If avg_elapsed_ms >> avg_cpu_ms (see R8 blocking signal), investigate locking with /sqlwait-review or /sqldeadlock-review. If avg_elapsed_ms ≈ avg_cpu_ms, the work itself is expensive — run /sqlplan-review.

R4 — Execution Frequency Hotspot

• Trigger: execs_in_interval ≥ 10,000 in a single collection window
• Severity: Warning
• Fix: A highly executed object deserves extra scrutiny even if per-execution cost is low. Total resource consumption is execution count × per-call cost. Pair with R11 (N+1) and R12 (chatty) checks to understand the caller pattern.

R5 — Physical I/O Hotspot (Cache Miss)

• Trigger: physical_reads_delta > 0 AND physical_pct > 10% (more than 1 in 10 page reads goes to disk)
• Severity: Warning if physical_pct > 10%; Critical if > 50%
• Fix: Data is not in the buffer pool. Root causes: (1) insufficient memory — check sys.dm_os_memory_clerks; (2) scans reading cold data — add covering indexes; (3) first execution after cache flush — monitor over multiple windows to see if physical reads decline.

---

Node-Level Checks (R6–R10): Per-Execution Efficiency

Apply these regardless of execution count — an infrequent but expensive proc matters.

R6 — High Average CPU per Execution

• Trigger: avg_cpu_ms ≥ 1,000 ms
• Severity: Warning if ≥ 1,000 ms; Critical if ≥ 10,000 ms
• Fix: The object does significant work per call. Capture its actual execution plan (SET STATISTICS TIME ON or SSMS actual plan) and run /sqlplan-review. Focus on the highest-cost operator. Common causes: full table scans from missing indexes, sort spills, hash join spills, large row sets.

R7 — High Average Reads per Execution

• Trigger: avg_logical_reads ≥ 50,000 per execution
• Severity: Warning if ≥ 50,000; Critical if ≥ 500,000
• Fix: The object reads many buffer pool pages per call. At 8 KB per page, 50,000 reads = 400 MB of data touched per execution. Run /sqlindex-advisor on its plan to generate covering index DDL. Check for table scans, key lookups, and missing WHERE clause indexes.

R8 — CPU-Elapsed Skew (Parallelism Waste or Blocking Signal)

• Trigger:
• cpu_to_elapsed_ratio > 1.5: CPU > elapsed → object uses parallelism, but threads may be poorly utilized (CXPACKET waits)
• cpu_to_elapsed_ratio > CPU → most time is waiting, not executing (blocking, lock waits, I/O)
• Severity: Warning if ratio > 1.5 or 3.0 or 50% of total total_worker_time_delta across all rows in the result set, OR the top 3 account for > 90%
• Severity: Info if top 3 > 70%; Warning if top 1 > 50%; Warning if top 3 > 90%
• Fix: The workload is concentrated on a small number of objects. This is common and not inherently a problem — but it means tuning R1/R2/R6/R7 on the top object will have outsized impact. It also means if that object fails or degrades, the entire server is affected. Consider read-scale, caching, or workload distribution.

R15 — Infrequent but Expensive

• Trigger: execs_in_interval ≤ 5 AND avg_logical_reads ≥ 100,000
• Severity: Info
• Fix: The object runs rarely but is extremely expensive per call — likely a batch job, report, or maintenance procedure. Even at low frequency, a 500,000-read execution blocks the buffer pool for other queries. Optimize with covering indexes (see R7), or schedule during off-peak hours with Resource Governor to cap its memory and CPU impact.

---

Trend Checks (R16–R20): Requires Q5 Time-Series Output

Skip these checks and state "Cannot evaluate R16–R20 — Q5 trend data not provided" if the input does not contain multiple rows per object across different collection_time values.

R16 — Worsening CPU Trend

• Trigger: avg_cpu_ms or cpu_ms_per_sec increases monotonically across ≥ 3 consecutive snapshots for the same object
• Severity: Info (2 snapshots worsening); Warning (≥ 3 consecutive)
• Fix: The procedure is getting slower over time. Causes: (1) data growth making plans suboptimal — rebuild indexes and update statistics; (2) plan regression from statistics update — use Query Store to identify and force the prior good plan; (3) application change increasing workload per call — instrument with SET STATISTICS IO, TIME ON.

R17 — Execution Rate Spike

• Trigger: execs_in_interval for the most recent snapshot > 2× the mean of prior snapshots for the same object
• Severity: Warning if > 2× mean; Critical if > 5× mean
• Fix: Something caused a sudden surge in call frequency — a batch job, application retry loop, or viral traffic. Identify the caller from sys.dm_exec_sessions or Extended Events. Check for application loops triggered by errors (error → retry → error spiral). Reduce call frequency or add circuit-breaker logic.

R18 — Read Regression

• Trigger: avg_logical_reads increases by > 50% between the oldest and newest snapshot for the same object
• Severity: Warning
• Fix: The procedure is reading more pages per execution than it used to. Common causes: (1) plan regression — capture before/after plans with /sqlplan-compare; (2) data growth making a seek read more rows; (3) index dropped or disabled. Compare plan_handle across snapshots (see R20 — if it changed, the plan regressed).

R19 — New High-Cost Entry

• Trigger: An object appears in the result for the first time (no prior rows in the time window) AND cpu_ms_per_sec ≥ 50 OR avg_logical_reads ≥ 50,000
• Severity: Info
• Fix: A new or previously-idle procedure appeared with high resource usage. This may indicate: (1) a newly deployed stored procedure; (2) a batch job that started running; (3) a procedure that was previously executing cheaply but now has a bad plan. Capture its execution plan and run /sqlplan-review.

R20 — Plan Instability Signal (Trend)

• Trigger: The same database_name + object_name shows different plan_handle values across consecutive snapshots in the trend output
• Severity: Warning
• Fix: The execution plan changed during the monitoring window — plan recompilation, cache eviction, or statistics update triggered a new plan. Check whether avg_cpu_ms or avg_logical_reads worsened after the plan change (correlate with R16/R18). If so, this is a plan regression — use Query Store to force the prior plan while investigating the root cause.

Advanced Runtime Pattern Checks (R21–R25)

R21 — Natively Compiled Proc Regression

• Trigger: A natively compiled procedure (sys.sql_modules.uses_native_compilation = 1) shows avg_cpu_ms increasing by ≥ 100% across snapshots — SQL 2014+ (In-Memory OLTP). Note: per-execution stats for natively compiled procedures appear in sys.dm_exec_procedure_stats only when statistics collection is enabled (sys.sp_xtp_control_proc_exec_stats)
• Severity: Warning — natively compiled procedures are expected to be extremely fast (sub-millisecond); CPU increase signals schema change, statistics shift, or lock contention on memory-optimized tables
• Fix: Recompile the procedure: EXEC sp_recompile N'schema.proc'. Check for schema changes to underlying memory-optimized tables. Verify the underlying tables' DURABILITY = SCHEMA_AND_DATA setting is intact and no new row-version contention has emerged. Note that STATISTICS IO reports 0 logical reads for memory-optimized tables — compare worker time instead.

R22 — High CLR Assembly Execution Ratio

• Trigger: A CLR procedure (sys.dm_exec_procedure_stats.type = 'PC') or a statement with total_clr_time / total_elapsed_time ≥ 40% from statement-level stats (sys.dm_exec_query_stats.total_clr_time — CLR time is not exposed in sys.dm_exec_procedure_stats) — indicates CLR assembly code dominates execution time
• Severity: Warning — CLR integration is not always slower than T-SQL, but a ratio ≥ 40% of elapsed time warrants review; CLR assemblies bypass the query optimizer and can mask inefficient .NET code paths
• Fix: Profile the CLR assembly itself (if source is available) using Visual Studio or dotnet-trace. If the CLR assembly performs I/O, consider moving that logic to T-SQL with indexed access. Evaluate whether the CLR function can be replaced by a built-in SQL Server function introduced in a later version (e.g., STRINGSPLIT, OPENJSON, STRINGAGG).

R23 — Trigger Dominating Proc Elapsed Time

• Trigger: Cross-referencing sys.dm_exec_trigger_stats with sys.dm_exec_procedure_stats reveals that a trigger's total_elapsed_ms is ≥ 50% of the DML procedure's total_elapsed_ms on the same table
• Severity: Warning — triggers add invisible overhead to DML; their cost does not appear in the procedure's own DMV entry and is often overlooked during tuning
• Fix: Identify triggers on the table: SELECT * FROM sys.triggers WHERE parent_id = OBJECT_ID('schema.table'). Run the trigger body through /tsql-review and capture its execution plan. Consider whether trigger logic can be moved to the application layer or batched asynchronously (e.g., via Service Broker or a background job).

R24 — Parallel Proc Became Serial (CPU/Elapsed Ratio Drop)

• Trigger: The same procedure shows avg_cpu_ms / avg_elapsed_ms ratio decreasing from ≥ 1.5 to ≤ 1.0 across consecutive snapshots — signals transition from a parallel to a serial plan
• Severity: Warning — a parallel plan becoming serial is a plan regression; elapsed time typically increases while CPU drops, indicating lost parallelism
• Fix: Check Query Store for plan changes: SELECT * FROM sys.query_store_plan WHERE query_id = (SELECT query_id FROM sys.query_store_query WHERE query_hash = 0x). If a MAXDOP change, statistics update, or schema change caused the plan regression, use Query Store to force the parallel plan. Run /sqlplan-review on both plans to confirm the DOP change (check S7, N30).

R25 — QS Plan Instability Correlated to Procstats Variance

• Trigger: A procedure appears in Query Store sys.query_store_plan with ≥ 3 distinct plans AND avg_cpu_ms variance between proc_stats snapshots is ≥ 50% — SQL 2016+ (Query Store)
• Severity: Warning — high variance in proc stats correlated with multiple Query Store plans indicates parameter-sensitive or plan-cache-churn behavior; each plan change risks a performance cliff
• Fix: Force the best plan in Query Store: EXEC sys.sp_query_store_force_plan @query_id = , @plan_id = . Investigate why plans are changing: statistics updates, ad-hoc parameter values, or RECOMPILE hints. Use /sqlquerystore-review (Q7–Q12) for full plan instability analysis.

---

Version-Aware Check Suppression

If the SQL Server version is stated by the user, read VERSION_COMPATIBILITY.md (`~/.claude/skills/VERSION_COMPATI

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: vanterx
• Source: vanterx/mssql-performance-skills
• License: MIT

Install and usage instructions live in the source repository linked above.