Extended Events for Proc Triage

Status: Reviewed (deep-dive v1)
Stack note: Lean XE sessions for warehouse timeouts/deadlocks — feeds SPIKE-05 / SQL-18/20. Not a full XE encyclopedia (leave broad tooling notes to SQL CLI where relevant).
Goal: Two practical sessions: blocking/deadlocks and long usp_* calls.

Quick chooser

Question	Session focus
Who blocked Ack?	`blocked_process_report` / deadlock graph
Which proc blew Blazor timeout?	`rpc_completed` / `sql_batch_completed` with duration filter
Plan flips?	Prefer Query Store (SQL-20); XE for waits detail

Lean: long-running procs

CREATE EVENT SESSION [Warehouse_LongRpc] ON SERVER
ADD EVENT sqlserver.rpc_completed (
  ACTION (sqlserver.database_name, sqlserver.username, sqlserver.sql_text)
  WHERE duration > 5000000  -- 5 seconds (microseconds)
    AND sqlserver.database_name = N'YourWarehouse'
)
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY = 4096 KB, STARTUP_STATE = OFF);
-- ALTER EVENT SESSION [Warehouse_LongRpc] ON SERVER STATE = START;

Prefer event_file target in prod for retention; ring_buffer for short labs.

Lean: deadlocks

CREATE EVENT SESSION [Warehouse_Deadlocks] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (SET filename = N'D:\XE\warehouse_deadlock')
WITH (STARTUP_STATE = ON);

Habits

[ ] Filter to warehouse DB — don’t capture estate-wide noise
[ ] Turn off when done; watch file growth
[ ] Tie findings to proc names → fix lock order / indexes
[ ] Alert on deadlock count via SQL-10 if chronic
[ ] Don’t run sp_trace classics as the default anymore

Done when

[ ] Documented start/stop for LongRpc + Deadlocks sessions
[ ] SPIKE-05 runbook links here for “how we captured it”
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-28 Extended Events for Proc Triage v1