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