Sqltrace 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 | logicalreads ≥ 100,000 | | High reads — critical | logicalreads ≥ 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 query event where cpu ≥ 5,000 ms
• Severity: Warning
• Fix: High CPU indicates scans, large sorts, hash joins, or implicit conversions. Use /sqlplan-review to find the dominant operator. Use /sqlindex-advisor for covering index recommendations.

X3 — High Logical Reads Query

• Trigger: Any completed query event where logical_reads ≥ 100,000 (warning) or ≥ 1,000,000 (critical)
• Severity: Warning (≥ 100 K); Critical (≥ 1 M)
• Fix: Run /sqlstats-review on this query's STATISTICS IO output to identify the highest-read table. Run /sqlindex-advisor to get a covering index. Each 8 KB page read = ~8 MB of data accessed.

X4 — High Write Count

• Trigger: Any completed query event where writes ≥ 10,000 pages
• Severity: Warning
• Fix: Large write counts indicate bulk DML, large sorts spilling to tempdb, or excessive worktable writes. If the query is a SELECT, writes indicate a tempdb spill — check X9 (Sort Warning) and X10 (Hash Warning). If DML, verify it was intentional and consider batching (see /tsql-review W7).

X5 — Attention Event (Client Timeout or Cancel)

• Trigger: Any event with class 16 (Attention) or XE event attention
• Severity: Warning
• Fix: The client disconnected or cancelled the query — either a command timeout was hit or the user cancelled manually. The query was running long enough to trigger the client's timeout. Run /sqlplan-review on the query to understand why it runs long. Consider increasing timeout only after optimizing the query.

X6 — Lock Timeout Event

• Trigger: Any event with class 54 (Lock:Timeout) or XE event lock_timeout
• Severity: Warning
• Fix: A session waited for a lock and timed out (LOCKTIMEOUT setting > 0). The blocking session holds a lock this query needs. Investigate: add a missing index to reduce lock duration, switch to READCOMMITTED_SNAPSHOT isolation, or use /sqldeadlock-review if deadlock graphs are also present.

X7 — Recompile Event

• Trigger: ≥ 3 recompile events (class 37 or 50, XE sql_statement_recompile) for the same stored procedure or normalized query within the trace window
• Severity: Warning
• Fix: Repeated recompilations are CPU-expensive and indicate plan instability. Common causes: schema changes to referenced objects mid-execution, SET option changes between calls, table variable row count changes after first reference, use of OPTION(RECOMPILE) in a hot path, or statistics updates. See /tsql-review T28 for OPTION(RECOMPILE) trade-offs.

X8 — Exception / Error Event

• Trigger: Any event with class 33 (Exception) or XE error_reported where severity 10 × min(duration)`
• Severity: Warning
• Fix: The cached plan was compiled for one parameter value but executes poorly for others. Fixes ranked by impact: (1) OPTION(RECOMPILE) on the query — per-execution plan, eliminates sniffing; (2) OPTION(OPTIMIZE FOR (@param = typical_value)) — pins a representative plan; (3) separate stored procedures for high/low cardinality paths; (4) use Query Store to force the good plan. Use /sqlplan-compare to diff the fast and slow plans.

X15 — Ad-Hoc / Unparameterized Workload

• Trigger: Distinct normalized query texts / total query events > 80%, OR large number of near-identical queries with embedded literals (e.g., WHERE Id = 1, WHERE Id = 2, ..., WHERE Id = N)
• Severity: Info
• Fix: The application is sending literal-embedded SQL rather than parameterized queries. Each distinct literal produces a unique plan cache entry — the plan cache fills with single-use plans, evicting useful plans. Fix: use sp_executesql with bound parameters, or ORM parameterization. Enable "optimize for ad hoc workloads" as a short-term mitigation (sp_configure 'optimize for ad hoc workloads', 1).

X16 — Excessive Global Recompilations

• Trigger: Recompile events (class 37 or 50) > 5% of total completed query events (class 10 + 12)
• Severity: Warning
• Fix: Global recompile pressure degrades the entire server — every recompile consumes CPU and a schema lock. Investigate the most-recompiled objects. Common causes: DDL on referenced objects (schema stability), SET option differences across connections, deferred compilation on temp tables (use OPTION(KEEP PLAN)).

X17 — Top Resource Consumers Summary

• Trigger: Always fires — this check always produces output
• Severity: Info
• Fix: No fix required for this check — it surfaces the top 5 queries by total CPU, total logical reads, and max duration. These are the highest-leverage targets for tuning. Run /sqlplan-review and /sqlindex-advisor on the top 1–3 entries.

X18 — Workload Concentration (Few Queries Dominate)

• Trigger: Top 3 normalized query patterns account for > 80% of total CPU time across all events
• Severity: Info
• Fix: Highly concentrated workloads are good news for tuning — fixing 3 queries improves the whole system. Focus effort entirely on those 3 queries before addressing anything else.

X19 — Auto-Grow Event Detected

• Trigger: Any event with class 92 (Data File Auto Grow) or 93 (Log File Auto Grow), or XE database_file_size_change with is_auto_grow = 1
• Severity: Warning (≥ 1 event in trace window, normal growth); Critical (≥ 5 events in trace window, frequent growth — file is sized too small for the workload)
• Fix: Auto-grow events pause all activity on the database while the file expands. Frequency matters more than individual duration: one 2-second auto-grow is less concerning than 50 auto-grows at 50 ms each — every grow pauses all database transactions. For data files: pre-size the file to avoid mid-workload grows; set instant file initialization (Windows privilege SE_MANAGE_VOLUME_NAME) to eliminate file zeroing on data file growth (not applicable to log files). For log files: either pre-size or investigate what is driving high log volume (large uncommitted transactions, bulk inserts without minimal logging, log backup frequency). If auto-grow duration exceeds 1,000 ms (slow auto-grow), the file system or storage subsystem cannot allocate space quickly enough — pre-size the file immediately. For any auto-grow that uses percent growth (the default on older SQL Server versions) rather than fixed-size growth, switch to fixed-size growth to avoid geometrically increasing growth amounts.

X20 — ShowPlan XML Events Present in Trace

• Trigger: Any event with class 146 (Showplan XML) or XE query_post_execution_showplan
• Severity: Info
• Fix: The trace captured execution plan XML inline. Extract the plan XML for the slowest queries and run /sqlplan-review on them directly — this is a richer artifact than trace metrics alone. Note that capturing Showplan XML for every query significantly increases trace overhead; disable this event class on production traces after initial diagnosis.

X21 — PSP Variant Switching in Trace

• Trigger: The same query_hash appears with ≥ 2 distinct plan_handle values AND duration variance ratio > 5× — SQL 2022+; flag as possible PSP variant switching if query_hash recurrence patterns show sub-second plan handle changes
• Severity: Warning — Parameter Sensitive Plan (PSP) variant switching produces multiple plans for the same query; when plans switch frequently it may signal that PSP thresholds are miscalibrated
• Fix: Confirm in Query Store: SELECT * FROM sys.query_store_plan WHERE query_id = (SELECT query_id FROM sys.query_store_query WHERE query_hash = 0x). If PSP variant plans are causing instability, consider OPTION(OPTIMIZE FOR UNKNOWN) or a Query Store hint to pin one plan.

X22 — XE Showplan Capture Overhead > 15%

• Trigger: Trace duration totals show XE session events with query_post_execution_showplan account for > 15% of total trace event count AND the trace window has > 1,000 events per minute
• Severity: Warning — Showplan XML capture at high event frequency creates observer overhead that slows the workload being diagnosed; the trace is changing the behavior it is observing
• Fix: Limit Showplan XML capture to specific query hashes using XE predicates: WHERE sqlserver.query_hash = 0x. After capturing one representative plan per query of interest, remove the Showplan event from the session. On production, prefer capturing plans via Query Store rather than inline XE Showplan.

X23 — Columnstore Delta Store Flush Frequency

• Trigger: XE columnstore_delta_store_flush events appear > 10 times within the trace window — SQL 2012+
• Severity: Info — frequent delta store flushes indicate that the columnstore is receiving frequent small inserts or DML rather than large bulk loads; this reduces compression efficiency and increases row-mode overhead
• Fix: Batch DML into larger sets (rows ≥ 102,400 per batch) to allow direct compressed segment insertion instead of delta store staging. Review the application's insert patterns. If real-time insert rate cannot be batched, enable delayed durability on the database for columnstore tables to reduce log flush overhead.

X24 — Ledger Block Generation Events

• Trigger: XE ledger_block_generated events present in trace — SQL 2022+ only; skip if event is absent
• Severity: Info — Ledger block generation events confirm that the SQL 2022 Ledger feature is active and generating cryptographic digests; the event itself is informational but high frequency (> 1/minute) may indicate unusually high ledger write activity
• Fix: Ledger blocks are generated per-transaction or per configured interval. High-frequency generation is expected with high ledger write volume. Monitor: SELECT * FROM sys.database_ledger_blocks ORDER BY block_id DESC. No tuning action unless ledger overhead is contributing to CPU contention.

X25 — ADR Version Cleaner Long-Duration Events

• Trigger: XE hadr_db_partner_set_sync_state or pvs_garbage_collection events with duration_ms > 5000 — SQL 2019+; skip if events absent
• Severity: Warning — ADR Persistent Version Store (PVS) garbage collection is taking > 5 seconds; this blocks version store space reclamation and can indicate long-running transactions preventing cleanup
• Fix: Identify blocking transactions: `SELECT * FROM sys.dmtranactivetransactions WHERE transactionbegin_time ) ← event-level (X1–X12)

[C1 — Pattern 3] Issue Name (X) ← workload aggregate (X13–X20)

• Observed: [query text snippet, metric value, SPID, timestamp or freq

…

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.