Lesson 7	Replaying traces
Objective	Replay a trace.

Replaying traces in SQL-Server

Question: Can SQL-Server replay a "trace file" in "Extended Events" using SQL Server Management Studio (SSMS):? Unfortunately, you cannot directly replay a SQL Server trace file within Extended Events using SQL Server Management Studio (SSMS). Here's why and what your alternatives are:
Why It's Not Possible

Different Technologies: SQL Trace and Extended Events are fundamentally different technologies for event capture and monitoring in SQL Server. They have distinct internal structures and formats for storing event data.
No Built-in Converter: SSMS doesn't have a built-in feature to automatically convert a SQL Trace file into the format used by Extended Events.

Alternatives

Distributed Replay (Limited Availability): For older versions of SQL Server (2019 and earlier), the Distributed Replay tool offers limited functionality to replay some SQL Trace workloads. However, this tool is no longer included in recent versions of SSMS.
RML Utilities: For newer SQL Server versions (2022 onwards), Microsoft recommends using Replay Markup Language (RML) Utilities, including the 'ostress' command-line tool, for replaying workloads.
Manual Conversion (Time-Consuming): While technically possible, manually converting a SQL Trace file into a series of Extended Events sessions would be very complex and time-consuming. This is not recommended unless you have a very specific reason and a deep understanding of both technologies.

If You Still Have Access to SQL Server 2019 or Earlier:

Open the trace file in SQL Server Profiler.
Use the 'Replay' feature within Profiler to simulate the workload.

For Newer SQL Server Versions: Familiarize yourself with RML Utilities to recreate and replay workloads. You'll likely need to capture traces in a format compatible with these utilities.
Important Note: Even with Distributed Replay or RML utilities, there may be limitations, and you might not be able to perfectly replicate all aspects of a trace file.

After you have run a trace and saved its output, SQL Server allows you to analyze what happened by replaying traces. Bear in mind that if you want to replay a trace, SQL Server imposes specific rules about the trace.

Trace Recorder — What you need to replay a trace

In order to replay a trace, you must, at a minimum, monitor these event classes:

Connect
Disconnect
ExistingConnection
RPC:Starting
SQL:BatchStarting
CursorExecute (if you want to replay server-side cursors)
CursorOpen (if you want to replay server-side cursors)
CursorPrepare (if you want to replay server-side cursors)
Exec Prepared SQL (if you want to replay server-side prepared SQL statements)
Prepare SQL (if you want to replay server-side prepared SQL statements)

Collect this information

View the image below to see the columns you must collect to replay a trace.

When replaying a trace you first must open the trace file or table by selecting File->Open->Trace File or File->Open->Trace Table. You then can use the Replay Trace menu to:

Start the trace
Walk through the trace step by step
Pause the trace
Set trace breakpoints

The following Diagram illustrates the features of the Replay Trace Window, which displays the trace information.

Top area with white background – Traced Event window displays all of the events that are in the trace table or trace file.

Bottom area with white background – Replayed Event window displays the events as they have been replayed, along with any errors that might be generated when replaying the trace.

Bottom gray error – More Information window displays details about the event that is selected or about the error that was generated.

The next lesson will show you how to use the Index Tuning Wizard.

Sql Server Profiler - Quiz

Click the Quiz link below to test your knowledge of the concepts presented in this module so far.
Sql Server Profiler - Quiz