Using the Replay feature
to test new code
This article answers a
common problem encountered in SQL Server development:
How do we develop code to ensure that it will not cause blocking in the production
We will solve this problem by capturing a SQL Trace against the Production server
and then rerunning that trace in our Development environment.
Reader has a basic familiarity with SQL Profiler and SQL Server.
NT 4.0 sp 4, SQL Server 7.0 sp 1
When developing a new piece
of code that will be installed in production it is useful to determine ahead
of time if the code will cause blocking on the server. To do this you need to
simulate the load of your production server, on your Development server. There
are several 3rd party tools available for doing this (Mercury Interactive’s http://www.merc-int.com
LoadRunner is one of them) but I use SQL Profiler. For this example our new
piece of code is a sproc (Stored Procedure) that deletes records from several
tables. This method of performance testing will work just as well when developing
anything from ActiveX COM objects to SQL Scripts. I took the following steps
to performance test this new sproc:
Capture SQL Trace of
Creating a trace for replay on another server is more tricky then creating a
trace to monitor server activity. When capturing a trace for replay you need
to capture certain Events and Data Columns or else the replay feature wont
work. Therefore, it is best to use the sample trace that is included with Profiler.
The sample is titled “Sample 6 – TSQL for Replay”. You can select the sample
trace by going File – Open – Trace Definition. Then select “Sample 6 – TSQL
for Replay” from the “Trace Name” drop-down. You will want to select your SQL
Server at this point as well. Select the name of your server from the drop-down
Trace Properties dialog selecting sample trace.