Four parts have been published till date, and for your reference, here are the links to them:

1. What is a SQL Trace?
2. Profiler Templates, Template Types and Creating Customized Templates
3. Trace execution options
4. Identify objects no longer supported by Microsoft - Deprecation Event

Today, we will be looking at another important application of the SQL Server Profiler – Replay. From Books-On-Line:

“Replay is the ability to save a trace and replay it later. This functionality lets you reproduce activity captured in a trace. When you create or edit a trace, you can save the trace to replay it later. SQL Server Profiler features a multithreaded playback engine that can simulate user connections and SQL Server Authentication. Replay is useful to troubleshoot an application or process problem. When you identify the problem and implement corrections, run the trace that found the potential problem against the corrected application or process. Then, replay the original trace and compare results.”

To demonstrate replay, let’s the following piece of code, which is rigged to fail. (There is no particular reason why I am using a code that is rigged to fail). I used this piece of code in my posts – Exception handling in T-SQL/TRY…CATCH – Underappreciated features of Microsoft SQL Server and Sunset for RAISERROR and sunrise for THROW – SQL 11 (“Denali”).

   IF EXISTS (SELECT * FROM sys.objects WHERE name = 'InnerProc' AND type = 'P')
        DROP PROCEDURE InnerProc
    GO

    CREATE PROCEDURE InnerProc
    AS
    BEGIN
        BEGIN TRANSACTION ExceptionHandling
           BEGIN TRY
              PRINT 'In the TRY block of the Inner Procedure...'
              SELECT 1/1

              RAISERROR('An error occured in the Inner procedure.',17,1)  --Line #10 considering CREATE PROC...as Line #1

              COMMIT TRANSACTION ExceptionHandling
           END TRY
           BEGIN CATCH
              SELECT ERROR_NUMBER() AS ErrorNumber
                    ,ERROR_SEVERITY() AS ErrorSeverity
                    ,ERROR_STATE() AS ErrorState
                    ,ERROR_PROCEDURE() AS ErrorProcedure
                    ,ERROR_LINE() AS ErrorLine
                    ,ERROR_MESSAGE() AS ErrorMessage;

              IF @@TRANCOUNT > 0
                 ROLLBACK TRANSACTION ExceptionHandling

              PRINT 'Throwing error from the CATCH block of the INNER Procedure...';   
              --Preceding statement MUST be a semi-colon ';'
              THROW
           END CATCH
    END
    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE name = 'OuterProc' AND type = 'P')
        DROP PROCEDURE OuterProc
    GO
    CREATE PROCEDURE OuterProc
    AS
    BEGIN
        BEGIN TRY
            PRINT 'In the TRY block of the Outer Procedure...'
            EXEC InnerProc
        END TRY
        BEGIN CATCH
            PRINT 'In the CATCH block of the Outer Procedure...';
            --Preceding statement MUST be a semi-colon ';'
            THROW
        END CATCH
    END
    GO

    --Executing the outer procedure
    EXEC OuterProc

Capturing the Replay trace

The SQL Server Profiler ships with a default “TSQL_Replay” trace. For this demo, we will be using this trace type.

As demonstrated in the previous posts of the series, capture the trace for the T-SQL script under review.

Replaying the trace

Open the Trace file saved during the trace collection

Notice the changes to the Profiler toolbar

In case one needs to stop execution at a certain step/statement, select the statement and hit F9 to set a breakpoint

Hit F5 or the yellow arrow key to start the Replay.

Apply the required Replay options.

Once the options are set, click on OK. Notice that the trace replays till the breakpoint is hit.

Alternatively, one can also use the F10 key to step-through the replay trace one step at a time or the (Ctrl + F10) combination to run to a selected cursor position.

Notice that during the replay options configuration, you can choose whether or not to have the SQL Server blocked process monitor running. This is especially useful if you are trying to replay a trace that you suspect is involved in heavy locking/blocking on your server.

Also notice the status bar, which now shows the number of open connections and the percentage of trace that has finished the replay.

In my next post…

In the next post, I will discuss how to correlate performance counters with the Profiler data.

References

1. Replay requirements
2. Replay Options
3. Considerations for Replaying Traces
4. Replay a Trace Table
5. Replay a Trace File
6. Replay a T-SQL Script
7. Replay to a Breakpoint
8. Replay a single event at a time