/sqlprocstats-review

name: sqlprocstats-review
description: Analyze SQL Server procedure/trigger/function runtime stats collected from sys.dm_exec_procedure_stats into collect.proc_stats. Applies 25 checks (R1–R25) across five categories — top consumers, per-execution efficiency, pattern detection, trend analysis, and advanced runtime patterns. Use when pasting output from the report queries in scripts/collection/04_report_queries.sql.
triggers:
  - /sqlprocstats-review
  - /proc-stats

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 | max_cpu_ms / avg_cpu_ms — parameter sniffing signal | | `cpu_to_elapsed_ratio` | computed | avg_cpu / avg_elapsed — > 1.5 = parallel; < 0.2 = blocking/IO | | `sample_seconds` | delta SP | Duration of the collection interval | | `cache_age_minutes` | computed | How long the current plan has been in cache |

---

Thresholds Reference

| Metric | Info | Warning | Critical | |--------|------|---------|----------| | `cpu_ms_per_sec` (single proc) | — | ≥ 50 ms/s | ≥ 500 ms/s | | Single proc share of total CPU delta | — | > 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) | — | < 0.2 | < 0.05 | | `max_to_avg_cpu_ratio` (sniffing signal) | ≥ 3 | ≥ 10 | ≥ 100 | | Top 3 procs share of total CPU delta | ≥ 70% | ≥ 90% | — | | `avg_elapsed_ms` per execution | — | ≥ 5,000 ms | ≥ 30,000 ms | | Trend: execution rate spike | — | latest > 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; hig