Using the Replay feature to test new code
This article answers a common problem encountered in SQL Server development
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.
After selecting the trace name and server we will want to specify some filters
for our trace. The sproc I'm developing archives 6 tables in the database. Therefore
I will want to filter the trace to only capture SQL executed against those tables.
I open up "Trace Properties" click on the Filters tab. Then scroll down to the
Text key and enter the following in the Include field: %<table 1 name>%;%<table
2 name>%;%<table nname>%;
Figure 2. Trace Properties dialog filtering by table name
Then I save the trace definition. You will want to be cautious
when filtering by table. If there was a primary key - foreign key relationship
defined between one of the tables I included in the trace and another table
I excluded I could encounter SQL errors when replaying my trace. In my case,
there is no pk-fk constraint defined so I am safe. Now our trace will only capture
SQL executed against the tables we entered above. After specifying the filter
our trace is ready to be executed against the production server. However, we
will want to keep in mind the time that my new sproc will run. In order to minimize
load on the server it was decided to install the sproc at 12:00 AM. Since installation
occurs at 12:00 AM we will want to ensure that our SQL trace captures activity
on the production server during the same time. So we've waited around until
midnight and now we can start the trace capture. We should let the trace run
for at least the length of time that I estimate the new sproc will run which
is, in this case, 5 hours. After 5 hours of running we stop the trace (click
on the Red square) and save the trace file (select Save As-Trace File from the
Replay the trace against
the Development server
After capturing the trace file we can now replay that trace file in our Development
environment. There are several "gotchas" that you will want to keep in mind
when replaying a trace on another server. Most of these are mentioned in bol
(books online) but they bear repeating here:
Gotcha 1: Replaying
the trace will only work if the users used SQL Server authentication to login
to the Production server. Any users logging in with NT authentication will
not be able to login.
Gotcha 2: Users and logins that exist in the trace capture must also
exist on the Development server. They must have adequate permissions against
the objects referenced in the trace file.
Gotcha 3: The dbid of the database in Development must match the dbid
of the database in Production. You can determine the dbid of your database
with the following query:
Gotcha 4: The passwords used by SQL Server logins must match the password
of the SQL user replaying the trace. The easiest way to deal with this is
to set all of the user passwords involved in the trace to the same password.
Then register the server in Profiler as a user with that same password. For
example, there were 3 users executing queries in the trace I captured: emailer,
reporter, and marker. I set each of these users passwords to "blah". I then
create another user "admin", assign him to the sysadmin role, and set his
password to "blah". Then I register the Development SQL Server in Profiler
using the "admin" user. So my steps were as follows:
WHERE name = '<database name here>'
- change the 3 users passwords to "blah":
EXEC sp_password '' , 'blah' , 'email'
EXEC sp_password '' , 'blah' , 'report'
EXEC sp_password '' , 'blah' , 'marker'
- create another user just for the purposes of running Profiler with the same
password as the users above:
EXEC sp_addlogin 'admin' , 'blah' , 'master'
EXEC sp_addsrvrolemember 'admin' , 'sysadmin'
- register SQL Server in Profiler using the admin user. If you have not
registered this server in Profiler before then do the following:
- select "Register SQL Server" from the Tools menu
- enter the name of the Server
- click on the "Use SQL Server authentication" radio button.
- Enter "admin" in the "Login Name" field.
- Enter "blah" in the Password field.
- Click on the Ok button
Figure 3. Entering login name and password for SQL Server
If you have already registered the server in Profiler you will need to change
the login parameters for the registration. Do the following:
- select "edit sql server
registration..." from the Tools menu
- select your server from
the list and click on the Edit button
- change the login and
password of the user to "admin" and "blah" respectively
- click on the ok button
There are other gotchas mentioned in bol but these were the only ones I encountered
when attempting to replay my trace. After taking into consideration all of the
gotchas and taking any action where appropriate I'm ready to replay the trace.
In order to replay the trace do the following:
- Open the trace file in Profiler (File - Open - Trace File...)
- After the trace file loads select Settings from the Replay menu
- Select your Development Server from the list of servers. If your server
is not in the list you will need to register it by selecting "<new server>"
- I select a Sync Level of "Full Synchronization" and a Replay rate of "Maintain
interval between events". For more information on how these options effect
the replay of your trace review bol.. Specify an output file if you wish to
record the results of your replay.
Figure 4. Selecting trace replay options
- After verifying all of
your options click on the yellow arrow button to start the replay. You should
see the output of the trace in the bottom half of the split screen:
Figure 5. Output from replaying trace appears in bottom pane of Profiler
- Any errors encountered
during the replay should appear in this section as well. Some common errors
you might encounter are:
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'marker'.
When you encounter this error verify the following:
- The existence of the user in your development environment
- The user's password must match the password of the user used to register the
SQL Server in Profiler (i.e. the user's password you see specified when you
select "Edit SQL Server Registration..." from the Tools menu, then select your
server and click Edit)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'authors'.
This error is most likely caused by the user's
default database not being set correctly. You can use the following sql if
you like to change the user's default database: sp_defaultdb '<login>'
- You can verify that the
trace is running by watching the bottom pane of the trace window for output
messages. Or you can execute sp_who2 in Query Analyzer and search for the logins
of the users in the trace capture.
- Execute your new code that
you wanted to test for blocking. In my case this is the new sproc that deletes
data from 6 tables in the database. While I'm replaying the SQL Trace I start
our new sproc. Then I monitor the server activity. The main thing I'm watching
for is blocking. You can do this either through Enterprise Manager or the sp_who2
sproc. If you get tired of manually executing sp_who2 every few minutes you
can write a script that automatically polls the server for blocking. I wrote
one and you can download it here if you like:
Is my new sproc causing blocking on the server?
Figure 6. Output from sp_who2 shows blocking on Server
screenshot shows us that the new sproc will cause some blocking on the server.
Whether or not this blocking is a problem depends on its duration. If we block
other processes for say 5 seconds that's not so bad. But if we were blocking
other processes for 3 minutes that could cause trouble. After monitoring the
server for the duration of the trace replay you should now have a good idea
of how the production server will react to your change. Did your change block
user processes for long periods of time? If so consider modifying the code to
reduce the chance of blocking. In our case the blocking lasted only for a few
seconds at a time. Therefore this new sproc can be installed into production.
the ability to replay SQL traces provides a powerful tool to help the developer
optimize his code. There are all sorts of neat things you can do when replaying
traces (i.e. breakpoints, pausing and starting the trace, stepping through the
trace, etc.). Although Profiler has its weaknesses the replay feature does a
great job of simulating a production environment on your Development server.