One fast query proves little about an upgrade or new index. A production workload replay brings concurrency, parameter mix, and timing into the test after Distributed Replay is gone.
Decide Which Production Workload to Capture
Distributed Replay is no longer part of newer SQL Server releases, but the need for a realistic test remains. Pick a representative period with the workload you intend to improve. Include busy and ordinary intervals when the change affects both. Capture statement or RPC completion events through Extended Events with duration, reads, CPU, database, and client context as needed. Filter to the application of interest and size the event file so capture overhead remains acceptable. I keep the capture window short and purposeful. Recording everything forever is not a test plan.
Protect the capture. Query text and parameters can contain private data. Store event files in an approved location, and sanitize or replace sensitive values before moving them to a replay environment.
Build the Restored Target
Restore a recent production backup to an isolated test SQL Server with comparable compatibility level, settings, schema, indexes, and statistics. Disable external side effects such as email, payment calls, and integration jobs. Use test identities. A replay can write data, so it needs a disposable target. I record hardware differences because they affect absolute duration. The useful comparison is before and after on the same test target under the same replay settings.
Start with a baseline replay before changing the index or upgrading compatibility level. Otherwise you have no control run. Reset the target from the same backup for each comparison, including cache warmup policy and background jobs.
Record the Production Workload With Extended Events
Create an event_file session in a test environment to learn the event fields and volume before using a production filter. The sample is a starting shape, not a production retention policy. Set the filename to an approved local path and stop the session at the end of the capture. The folder must already exist, and the SQL Server service account needs write access to it. Otherwise the START statement fails with error 25602. I check dropped-event counts and file rollover. A capture with missing events can understate concurrency and produce a suspiciously easy replay.
CREATE EVENT SESSION [WorkloadCapture] ON SERVER
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.database_id,sqlserver.client_app_name,sqlserver.session_id)),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.database_id,sqlserver.client_app_name,sqlserver.session_id))
ADD TARGET package0.event_file(SET filename=N'C:\SqlTrace\WorkloadCapture.xel');
ALTER EVENT SESSION [WorkloadCapture] ON SERVER STATE = START;Replay Through a Supported Tool
WorkloadTools can consume a captured workload and run it against the restored target. If you extract ordered commands into scripts, ostress from RML Utilities can execute those scripts with controlled concurrency. ostress does not directly turn an arbitrary Extended Events file into a faithful replay; that conversion step matters. Test one session first, then increase concurrency toward the observed pattern. Preserve think time and parameter distributions where the tool supports them. I inspect error logs from the replay so a fast run that skipped failing commands is not mistaken for an improvement.
Do not aim the tool at production. Use an isolated endpoint and an account with only the test database rights needed. The word "replay" sounds harmless until it runs an UPDATE twice.
Compare Before and After
Collect duration, CPU, logical reads, timeouts, errors, blocking, and throughput for both runs. Query Store can summarize statements on the target, while tool logs show client timing. Compare matching query families and the whole workload. A new index can improve a report and slow writes. An upgrade can change plans for a small subset of statements. I look at both tails and averages, not only one headline number. Use actual measured results from the test, never a number invented for the article.
Run the same capture against the same restored starting point with the single planned change. Repeat if variation is large. Label hardware and concurrency differences so the result is interpreted honestly.
Turn Capture Into a Replay Plan
An Extended Events file contains observations, not an executable script for ostress. Decide which event fields become commands, how parameter values are represented, and how concurrent sessions are grouped. WorkloadTools has capture and replay components designed for this job; test its chosen collection path against your SQL Server version before production capture. If you use ostress, extract and review scripts first, then set connection count and repetitions to approximate the observed load. I run a small subset and inspect errors before scaling up. A replay that fails every third statement can still finish quickly, which makes a misleading performance chart.
REM Command line
ostress -S TestSqlServer -d ReplayDb -E -i "C:\Replay\workload.sql" -n 8 -r 1Protect ordering where transactions depend on it. A simple batch of statements run concurrently does not reconstruct the exact transaction and session state of a live application. Keep the original capture timestamp, transformation script, and excluded event list with the test record. The gaps matter when interpreting a result.
Compare Errors Before Speed
Before discussing duration and reads, compare statement success, row effects, deadlocks, and timeout counts. A plan change that makes a query faster by skipping work is not an improvement. I restore the same starting backup before each replay and disable outgoing side effects, then compare application-visible outcomes where possible. If the workload includes nondeterministic values such as GETDATE(), decide how to hold them stable or explain the difference.
What will you do with statements that reference external services or production-only security? Exclude them with a written rule or replace them with safe equivalents. Do not silently omit them and call the resulting run "the production workload." I use replay as one strong performance test beside correctness and application tests. That combination catches far more than either a single query benchmark or a blind traffic recording.
Know What a Production Workload Replay Misses
A capture can omit application think time, connection behavior, transaction boundaries, session settings, temporary tables, or external services. Review the tool's fidelity for the event type you captured. A query-only replay is useful for plan and read comparisons, but it is not a full application acceptance test. What production behavior is missing from your rehearsal? Put that question in the test report.
I pair replay with targeted application tests for correctness and failover behavior. The replay tells me where the changed engine or index spends work under familiar pressure. It does not certify the whole release.
Related reading on this blog: Using Query Store to Prove an Upgrade Did Not Hurt and Stress Testing with oStress: Load Testing.
A workload replay is not a copy of production, it is a controlled test of its pressure.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
