Safely Testing SQL Server Performance Fixes: Capturing and Replaying Production Workloads

Every fix in this course has been validated by re-running “a” query. In real production incidents, you need to validate against the real, full mixed workload before deploying — a synthetic single-query test can miss regressions elsewhere.

Capturing a Real Workload

-- Capture via an Extended Events session (modern approach, replaces old SQL Trace .trc capture)
CREATE EVENT SESSION WorkloadCapture ON SERVER
ADD EVENT sqlserver.rpc_completed, ADD EVENT sqlserver.sql_batch_completed
ADD TARGET package0.event_file (SET filename = N'WorkloadCapture')
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
GO
ALTER EVENT SESSION WorkloadCapture ON SERVER STATE = START;
-- Let it run for a representative window (e.g. peak business hours), then STOP

Replaying It Against an Isolated Copy

Restore a recent production backup to a genuinely isolated environment — never replay a captured production workload against anything that could touch real production data (it contains real inserts/updates/deletes). Apply your candidate fix (new index, forced plan, Resource Governor config) there, then replay the captured trace and compare aggregate duration/CPU against the pre-fix baseline replay.

Why This Beats Testing One Query in Isolation

A new index that speeds up the one query you were chasing can slow down a dozen other write-heavy statements in the real mixed workload — something a single-query test will never reveal. Full workload replay is how you catch that before it reaches production, not after.

The Safety Rule

Never test against production. Never replay a real captured workload containing real data changes against anything other than a genuinely isolated copy — the whole point is safety, and that guarantee only holds if the target environment can’t affect anything real.