/sqltrace-review

name: sqltrace-review
description: Analyze SQL Server trace files and Extended Events output to identify workload-level performance patterns. Applies 25 checks (X1–X12 event-level, X13–X25 workload aggregate) covering long-running queries, high-frequency N+1 patterns, parameter sniffing signals, recompilations, lock timeouts, hash/sort warnings, top resource consumers, and SQL 2019/2022 modern feature events. Use when a user provides Profiler trace output, sys.fn_trace_gettable() results, or Extended Events session data.
triggers:
  - /sqltrace-review
  - /trace-review

SQL Server Trace / Extended Events Review Skill

Purpose

Analyze workload-level diagnostic data from SQL Server Profiler traces (`.trc`), Extended Events sessions (`.xel`), `sys.fn_trace_gettable()` output, or XE session query results. Produce a ranked summary of top resource consumers and a prioritized findings report covering 25 checks (X1–X25) across event patterns and cross-event workload aggregates.

Trace analysis reveals patterns that no single-query artifact can show: which queries run thousands of times per minute, which have wildly inconsistent durations (parameter sniffing), how many recompilations are happening globally, and whether spill or lock events correlate with slow periods.

Input

Accept any of:

• `sys.fn_trace_gettable()` query results — paste the tabular output (tab-separated, CSV, or grid)
• Extended Events session query results — any column layout containing event name, SQL text, duration, CPU, reads
• SSMS Profiler trace grid — copy-paste from the trace window
• A `.trc` or `.xel` file path (describe what to extract if the file cannot be read directly)
• A natural-language description of trace contents ("the trace shows 48,000 executions of a stored proc in 60 seconds, each reading 3,200 pages")

**Duration units:** SQL Profiler `.trc` Duration column = **microseconds**. Extended Events `duration` = **microseconds**. CPU units differ by event class: for `SQL:BatchCompleted` (EventClass 12), CPU is in **milliseconds**; for `RPC:Completed` (EventClass 10), CPU is in **microseconds beginning with SQL Server 2012 (11.x)**, and in **milliseconds in earlier versions**. On SQL Server 2008 R2 and earlier, all trace CPU values were in milliseconds. Normalize all duration values to milliseconds before applying thresholds and displaying results.

**Query normalization:** Group events by normalized query text — replace literal values and parameter values with placeholders to identify the same logical query across executions. Example: `SELECT * FROM Orders WHERE Id = 42` and `SELECT * FROM Orders WHERE Id = 99` normalize to the same pattern.

How to Run

1. **Parse input**: identify which columns are present. Map to canonical fields: `event_class`, `sql_text`, `duration_us`, `cpu_ms`, `logical_reads`, `writes`, `spid`, `app_name`, `login_name`, `db_name`, `start_time`. 2. **Classify events**: use event class number or XE event name to categorize each row (see Event Class Reference below). 3. **Normalize queries**: group `SQL:BatchCompleted` and `RPC:Completed` events by normalized query pattern. Compute per-pattern: execution count, total/avg/min/max for duration, CPU, reads. 4. **Run X1–X12 (event-level checks)**: scan each event row for individual threshold violations. 5. **Run X13–X20 (workload-level checks)**: aggregate across all events and normalized patterns. 6. **Build top-consumer tables**: top 5 by CPU, by reads, by duration. 7. **Output**: produce the structured report defined in Output Format.

---

Event Class Reference

| Profiler Class | XE Event Name | Category | |---------------|---------------|----------| | 10 | `rpc_completed` | Query | | 12 | `sql_batch_completed` | Query | | 13 | `sql_batch_starting` | Query | | 16 | `attention` | Connection | | 20 | `error_reported` (login fail) | Security | | 37 | `sql_statement_recompile` | Recompile | | 50 | `sql_statement_recompile` | Recompile | | 54 | `lock_timeout` | Locking | | 59 | `xml_deadlock_report` | Locking | | 65 | `hash_warning` | Warning | | 69 | `sort_warning` | Warning | | 79 | `missing_column_statistics` | Statistics | | 80 | `missing_join_predicate` | Warning | | 92 | `data_file_auto_grow` | Storage | | 93 | `log_file_auto_grow` | Storage | | 146 | `query_post_execution_showplan` | Plan |

---

Thresholds Reference

| Metric | Value | |--------|-------| | Long duration — warning | duration ≥ 5,000 ms | | Long duration — critical | duration ≥ 30,000 ms | | High CPU — warning | cpu ≥ 5,000 ms | | High reads — warning | logical_reads ≥ 100,000 | | High reads — critical | logical_reads ≥ 1,000,000 | | High writes — warning | writes ≥ 10,000 pages | | Error severity — critical | error severity ≥ 20 | | Recompile threshold | ≥ 3 recompile events for the same object/query in trace window | | High-frequency query | ≥ 1,000 executions of the same normalized query | | Parameter sniffing signal | max duration > 10× min duration, same normalized query, ≥ 10 executions | | Global recompile ratio | recompile events > 5% of (SQL:BatchCompleted + RPC:Completed) events | | Workload concentration | top 3 normalized queries > 80% of total CPU | | Ad-hoc ratio | distinct query texts / total query events > 80% |

---

Event-Level Checks (X1–X12)

Evaluate per-event rows. A check fires if any single event meets its trigger condition.

X1 — Long-Duration Query

• **Trigger:** Any `SQL:BatchCompleted`, `RPC:Completed`, or `sql_statement_completed` event where `duration ≥ 5,000 ms` (warning) or `≥ 30,000 ms` (critical). Duration column is in microseconds — divide by 1,000 before comparing.
• **Severity:** Warning (5 s – 29.9 s); Critical (≥ 30 s)
• **Fix:** Capture the execution plan for this query and run `/sqlplan-review`. Run `/sqlstats-review` on `SET STATISTICS IO, TIME ON` output. Identify whether the query is CPU-bound (X2) or wait-bound (high duration, low CPU).

X2 — High CPU Query

• **Trigger:** Any completed quer