Finding Problem Queries with SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 115

Simulating a Scenario and a Sample Database
The purpose of this scenario is to have SQL Profiler identify the longest running queries in a workload.
To simulate a set of long-running queries, you first create a sample database.

To create the sample database ( people2 ), you can download source code from the publisher's web site
at www.wrox.com . All T-SQL scripts are stored in the People2 folder. Instructions are provided in the
readme.txt file. You will execute Step1_CreatePeople2Database.sql to create the sample database,
and then execute Step2_CreateTablesProcs.sql to create database tables and stored procedures. Lastly,
execute Step3_InitializeNames.sql to initialize the testing data for our scenario.

The database has a key table called people that you want to generate about 400,000 rows. Each row
contains data for the first name, last name, and date of birth. A T-SQL script called populate.sql will
find a male entry and update a female entry to match the male last name.

Depending on your server speed, the populate.sql script may take about 3-4 minutes to finish.

Analyzing the Issue
The definition of long-running queries can be arbitrary. For example, the threshold value may be defined
by a set of business requirements. Using SQL Profiler to identify the top 10 long-running queries is
not difficult. In this scenario, you will use a T-SQL script populate.sql to generate long-running queries.

The first challenge is to match event classes with the issue. Please reference Table 5-1 for suggested event
classes. Use the following event classes. A query execution time is recorded in the duration column.

❑ RPC:Completed: Indicates that a remote procedure call has been completed. This event will

capture stored procedure calls from client connections.

❑ SQL:BatchCompleted: Indicates that the Transact-SQL batch has been completed.

❑ SP:StmtCompleted: Indicates that a Transact-SQL statement within a stored procedure has

been completed.

The second challenge is to set up a filter to collect the right amount of data. Calibrate a small sample
workload first. After analyzing the sample workload, this new information will help to determine what
exact filters to use and what threshold values to apply.

For this scenario, you will launch the Profiler on the same server and not use the Save To File option to
save trace data to a file, since you will be monitoring the activities.

Setup
The following steps include initial setup, validation, and finding filter criteria:

1.

Start SQL Profiler. In SQL Server Management Studio, click the Tools menu, then select
SQL Server Profiler. (Alternatively, start SQL Profiler by clicking Start

➪

Run, then type

in Profiler90 ).

2.

From the File menu, click New Trace.