Based on popular demand from my colleagues and you, the kind reader, I am currently writing a series of posts on the SQL Server Profiler. Four parts have been published till date, and for your kind reference, here are the links to them:

1. What is a SQL Trace? Permissions, space requirements and launching the Profiler
2. Profiler Templates, Template Types & creating custom templates
3. Trace execution options – Save trace to file or a table, Auto-scroll and keyboard shortcuts
4. Review T-SQL code to 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…

The next post will be the final one of the series, in which I will discuss how to correlate performance counters with the Profiler data.

If you have suggestions on any applications of the Profiler that you want me to cover, drop me a note, and I will try to cover them in future posts.

References

• Replay requirements
• Replay Options
• Considerations for Replaying Traces
• Replay a Trace Table
• Replay a Trace File
• Replay a T-SQL Script
• Replay to a Breakpoint
• Replay a single event at a time

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!