# Sqlprocstats Review

> 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.

- **Type:** Skill
- **Install:** `agentstack add skill-vanterx-mssql-performance-skills-sqlprocstats-review`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [vanterx](https://agentstack.voostack.com/s/vanterx)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [vanterx](https://github.com/vanterx)
- **Source:** https://github.com/vanterx/mssql-performance-skills/tree/main/skills/sqlprocstats-review

## Install

```sh
agentstack add skill-vanterx-mssql-performance-skills-sqlprocstats-review
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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;  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., STRING_SPLIT, OPENJSON, STRING_AGG).

### 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](https://github.com/vanterx)
- **Source:** [vanterx/mssql-performance-skills](https://github.com/vanterx/mssql-performance-skills)
- **License:** MIT

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

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-vanterx-mssql-performance-skills-sqlprocstats-review
- Seller: https://agentstack.voostack.com/s/vanterx
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.