Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using the REPLAY feature within SQL Server Profiler


By:   |   Last Updated: 2013-09-18   |   Comments   |   Related Tips: More > Profiler and Trace

Problem

Have you ever had the need to reproduce a specific issue from production in another environment for debugging or wanted the ability to test the exact same data load on a different server configuration or hardware? Available since SQL Server 2005 is the trace replay functionality within SQL Profiler which allows us to do exactly what is described above. This tip will walk through a simple example to demonstrate how this feature can be used.

Solution

Table Setup to capture SQL Server Profiler Data

Before we can get started with running a trace we'll need a table and script that we can use to run the trace against. The first script below creates a simple table with a few different column datatypes. The second script we will run while the trace is running so we can capture INSERT, UPDATE, DELETE and SELECT activity to be replayed.

CREATE TABLE [dbo].[TABLE1](
 [pkcol] [int] NOT NULL,
 [datacol1] [int] NULL,
 [datacol2] [int] NULL,
 [datacol3] [varchar](50) NULL,
 [datacol4] [datetime] NULL,
 CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED ( [pkcol] ASC ) 
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SELECT GETDATE(),'Start insert'
GO
-- INSERT DATA
INSERT INTO dbo.Table1(pkcol, datacol1, datacol2, datacol3, datacol4) 
 VALUES (1,1,1,'TEST',getdate()-1)
GO
WAITFOR DELAY '00:00:01'
GO
INSERT INTO dbo.Table1(pkcol, datacol1, datacol2, datacol3, datacol4)
 VALUES (2,2,2,'TEST',getdate()-2)
GO
WAITFOR DELAY '00:00:01'
GO
INSERT INTO dbo.Table1(pkcol, datacol1, datacol2, datacol3, datacol4)
 VALUES (3,3,3,'TEST',getdate()-3)
GO
WAITFOR DELAY '00:00:01'
GO
INSERT INTO dbo.Table1(pkcol, datacol1, datacol2, datacol3, datacol4)
 VALUES (4,4,4,'TEST',getdate()-4)
GO
WAITFOR DELAY '00:00:01'
GO
INSERT INTO dbo.Table1(pkcol, datacol1, datacol2, datacol3, datacol4)
 VALUES (5,5,5,'TEST',getdate()-5)
GO
WAITFOR DELAY '00:00:01'
GO
SELECT GETDATE(),'End insert'
GO
SELECT GETDATE(),'Start update'
GO
-- UPDATE DATA
DECLARE @updval INT
SELECT TOP 1 @updval=pkcol FROM dbo.TABLE1 ORDER BY NEWID()
UPDATE dbo.TABLE1 
   SET datacol1=1+50000, datacol2=1+50000, datacol3='TESTUPD', datacol4=getdate()+365
 WHERE [email protected]
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @updval INT
SELECT TOP 1 @updval=pkcol FROM dbo.TABLE1 ORDER BY NEWID()
UPDATE dbo.TABLE1
   SET datacol1=2+50000, datacol2=2+50000, datacol3='TESTUPD', datacol4=getdate()+365
 WHERE [email protected]
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @updval INT
SELECT TOP 1 @updval=pkcol FROM dbo.TABLE1 ORDER BY NEWID()
UPDATE dbo.TABLE1
   SET datacol1=3+50000, datacol2=3+50000, datacol3='TESTUPD', datacol4=getdate()+365
 WHERE [email protected]
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @updval INT
SELECT TOP 1 @updval=pkcol FROM dbo.TABLE1 ORDER BY NEWID()
UPDATE dbo.TABLE1 
   SET datacol1=4+50000, datacol2=4+50000, datacol3='TESTUPD', datacol4=getdate()+365
 WHERE [email protected]
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @updval INT
SELECT TOP 1 @updval=pkcol FROM dbo.TABLE1 ORDER BY NEWID()
UPDATE dbo.TABLE1 
   SET datacol1=5+50000, datacol2=5+50000, datacol3='TESTUPD', datacol4=getdate()+365
 WHERE [email protected]
GO
WAITFOR DELAY '00:00:01'
GO
SELECT GETDATE(),'End update'
GO
SELECT GETDATE(),'Start delete'
GO
-- DELETE DATA
DECLARE @delval INT
SELECT TOP 1 @delval=pkcol FROM dbo.TABLE1 ORDER BY NEWID()
DELETE FROM dbo.TABLE1 WHERE [email protected]
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @delval INT
SELECT TOP 1 @delval=pkcol FROM dbo.TABLE1 ORDER BY NEWID()
DELETE FROM dbo.TABLE1 WHERE [email protected]
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @delval INT
SELECT TOP 1 @delval=pkcol FROM dbo.TABLE1 ORDER BY NEWID()
DELETE FROM dbo.TABLE1 WHERE [email protected]
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @delval INT
SELECT TOP 1 @delval=pkcol FROM dbo.TABLE1 ORDER BY NEWID()
DELETE FROM dbo.TABLE1 WHERE [email protected]
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @delval INT
SELECT TOP 1 @delval=pkcol FROM dbo.TABLE1 ORDER BY NEWID()
DELETE FROM dbo.TABLE1 WHERE [email protected]
GO
WAITFOR DELAY '00:00:01'
GO
SELECT GETDATE(),'End delete'
GO
SELECT GETDATE(),'Start select'
GO
-- SELECT DATA
SELECT TOP 1 * FROM dbo.TABLE1 ORDER BY NEWID()
GO
WAITFOR DELAY '00:00:01'
GO
SELECT TOP 1 * FROM dbo.TABLE1 ORDER BY NEWID()
GO
WAITFOR DELAY '00:00:01'
GO
SELECT TOP 1 * FROM dbo.TABLE1 ORDER BY NEWID()
GO
WAITFOR DELAY '00:00:01'
GO
SELECT TOP 1 * FROM dbo.TABLE1 ORDER BY NEWID()
GO
WAITFOR DELAY '00:00:01'
GO
SELECT TOP 1 * FROM dbo.TABLE1 ORDER BY NEWID()
GO
WAITFOR DELAY '00:00:01'
GO
SELECT GETDATE(),'End select'
GO

Running the SQL Server Trace

I'm sure most of us are familiar with running a SQL trace using Profiler, but for those of us that aren't this tip outlines the basics.

There are however, a few things specific to setting up a trace that need to be done if the trace file is going to be used for replay at a later time. This link outlines these requirements which mainly entail having specific event classes and data columns selected in your trace configuration. Luckily SQL Profiler comes with a trace template that includes these settings called "TSQL_Replay". I usually use this template as the base for my profiler settings and add any other events/columns I think are necessary.

For this example, after starting up SQL Profiler you can configure the trace as shown in the two screenshots below. In the first screenshot you can see that I've updated the "Trace Name" and selected the "TSQL_Replay" template. I've also configured the trace to be saved to a file (although this could be done after you stop the trace). In the second screenshot you can see that I've added the CPU, Duration, Reads and Writes columns to the "RPC:Completed" and "SQL:BatchCompleted" events.

Trace Properties - General

Trace Properties - Events Selection

Now that we've configured the trace we can click the "Run" button to start it. Once running open up the test script from the table setup section in a new query window in SQL Server Management Studio and run it. After it's completed go back to SQL Profiler and stop the trace. You should now have a completed trace file in the location you specified when setting up the trace (if you did not configure this save the trace from the current window to somewhere on your filesystem) so we can close this window.

Replaying the SQL Server Trace

In order to replay the trace file we need to re-open it in SQL Profiler. Once the file has been read in you can see that the menu items under the "Replay" menu are now enabled (you'll notice if you looked at this menu item before closing the trace file they were all greyed out). Select "Start" under this menu and a window will pop up where we can configure the replay options. The first tab in this window is the Basic options. A complete description of all the options on this tab can be found here but for this tip we'll just configure a few of the options. We're going to first check the "Save to file:" and "Display execution time" checkboxes. Finally and probably the most useful option on this tab is the "Replay Server:" option. Although we are not going to do it for this tip, this is the option that would allow you to run this replay against a different server if required. Here is a screenshot of this tab after it's been completed.

Replay Configuration - Basic

The second tab in this window contains the advanced options. A complete description of all the options on this tab can be found here. For this tip we'll just leave all the defaults selected on this tab. Here is a screenshot of what's available on this tab.

Replay Configuration - Advanced

Now that we have configured all the required options we can click "OK" to start the replay. Once we click this button we see a second section open below the original trace which shows the results of our replay. You'll also notice a green arrow traversing through your original trace which highlights the statement that is currently being executed.

Trace Replay - Executing

Once the replay is complete you can go through the replay trace file and check for any errors and validate execution times. The replay trace also contains a "Replay Statistics Event" which provides a good summary or your replay session. Here is what the details of that trace event look like.

Trace Replay - Statistics Event
Next Steps


Last Updated: 2013-09-18


get scripts

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools