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
| 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!
SQLTwins by Nakul Vachhrajani 