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 20 checks (X1–X20) 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
.trcor.xelfile 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 = milliseconds throughout. Normalize all duration values to milliseconds before applying thresholds and displaying results.
More from vanterx/mssql-performance-skills
sqlplan-review
Analyze SQL Server execution plans for performance anti-patterns. Applies all 99 checks (S1–S33 statement-level, N1–N66 node-level). Use when a user pastes a .sqlplan XML, describes operators, or asks why a query is slow.
2sqlplan-deadlock
Analyze SQL Server deadlock XML (from system_health XE session, SSMS deadlock graph, or trace) to identify root cause and produce a prioritized remediation plan. Use when a deadlock monitor captures a graph or users report intermittent deadlock errors (error 1205).
1sqlplan-compare
Diff two SQL Server execution plans (baseline vs regression) to identify what changed — join strategies, memory grants, operator topology, new warnings, and missing indexes. Use when a query regressed after a deployment, statistics update, or schema change.
1procstats-review
Analyze SQL Server procedure/trigger/function runtime stats collected from sys.dm_exec_procedure_stats into collect.proc_stats. Applies 20 checks (R1–R20) across four categories — top consumers, per-execution efficiency, pattern detection, and trend analysis. Use when pasting output from the report queries in sql/collection/04_report_queries.sql.
1sqlstats-review
Parse and analyze SQL Server SET STATISTICS IO, TIME ON output. Extracts per-table IO metrics and per-statement CPU/elapsed times, computes % logical read share, detects 22 performance patterns (I1–I15 IO checks, W1–W7 time checks). Use when a user pastes SSMS statistics output or asks why a query does too much I/O.
1sqlwait-review
Analyze SQL Server wait statistics to identify why the server or a session is slow. Applies 40 checks (V1–V40) covering I/O, locks, parallelism, memory, CPU, TempDB, log I/O, network, latch contention, log space exhaustion, poison/throttle waits, backup I/O, insert hotspots, cumulative skew detection, multi-snapshot trend analysis, In-Memory OLTP, Columnstore, Query Store, Transaction/DTC, Service Broker, Full Text Search, Parallel Redo, forced memory grants, grant timeouts, stolen memory, and file I/O latency. Based on Paul Randal and Brent Ozar methodologies. Use when pasting sys.dm_os_wait_stats or sys.dm_exec_requests output.
1