Using SQL Server Profiler

Sunday Apr 9th 2000 by Andrew Wiegand

SQL Profiler Using the Replay feature to test new code This article answers a common problem encountered in SQL Server development

SQL Profiler
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 environment?
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 production load
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 list:

Figure 1.     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 File menu).

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:

FROM master.dbo.sysdatabases
WHERE name = '<database name here>'
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:

  1. change the 3 users passwords to "blah":
    EXEC sp_password '' , 'blah' , 'email'
    EXEC sp_password '' , 'blah' , 'report'
    EXEC sp_password '' , 'blah' , 'marker'
  2. 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'
  3. register SQL Server in Profiler using the admin user. If you have not registered this server in Profiler before then do the following:
  1. select "Register SQL Server" from the Tools menu
  2. enter the name of the Server
  3. click on the "Use SQL Server authentication" radio button.
  4. Enter "admin" in the "Login Name" field.
  5. Enter "blah" in the Password field.
  6. 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:

  1. select "edit sql server registration..." from the Tools menu
  2. select your server from the list and click on the Edit button
  3. change the login and password of the user to "admin" and "blah" respectively
  4. 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:

  1. Open the trace file in Profiler (File - Open - Trace File...)
  2. After the trace file loads select Settings from the Replay menu
  3. 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>"
  4. 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

  1. 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 window

  2. 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:
    1. The existence of the user in your development environment
    2. 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>' , '<database>'

  3. 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.

  4. 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

The above 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.

Currently 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.

Mobile Site | Full Site