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.