Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 9, 2000

Using SQL Server Profiler

By Andrew Wiegand

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM