Using SQL Server Profiler

April 9, 2000

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.