Show SQL Execution Plan on Trace Replay

Question

I'm using Microsoft SQL Server Profiler to capture production trace data for replay on a dev server to test indexing changes. Capturing the trace is no problem: I am using a the "TSQL_Replay" trace template with the "Showplan XML Statistics Profile" event added. In the original capture, I can see the execution plan in the trace data.

The problem is when I replay this trace data on the dev server. I don't see any option to configure the replay trace options. I can check "Display execution time" and get timings for individual queries but I can't see the execution plans that run when the trace is replayed. This is critical for determining how the new indexes are being used. I've tried running a separate parallel trace on the dev server while I replay the original trace, but replays don't seem to be picked up.

Any advice on how to measure results when replaying a trace?

I've tried playing with both RML Utilities and the Database Experimentation Assistant but haven't figured out how to get either of them working for this use-case.

• HandyD: When you run the parallel trace, is the default filter on ApplicationName that excludes Profile still enabled?
• Neil Laslett: @HandyD Great catch! I never thought to dig into the filters. Thank you, now I can capture the replay. I still need to better explore the RML and DEA tools but this helps a lot. Make this an answer so I can mark it.

Answer 1

By default, Profiler adds a filter to the ApplicationName column to filter itself out. Remove this filter and you should see the replay activity in Profiler.

Answer 2

I just turn on QueryStore on the target database. One tip would be to use DBCC CLONEDATABASE which will copy the schema, statistics and querystore data but not the user table contents.

There is a built in reporter tool but I prefer QueryStore.

• Neil Laslett: Good tip. I need to dig into QueryStore more. Thanks.