Professional SQL Server® 2005 Performance Tuning

5.5. Identifying Long-Running Queries Using SQL Profiler

Many situations require monitoring long-running queries. For example, an existing Service Level Agreement (SLA) might require that some items need to be measured by query response time. Database users might complain that some of the queries have inconsistent response time behaviors. Sometimes queries can execute slowly or cause application time-outs.

SQL Profiler is one of the most valuable graphical user interface (GUI) tools in determining which queries take the longest time to execute or confirming which specific queries run slowest. SQL Profiler can monitor query activity as it occurs.

The following simple scenario will walk through the process of identifying long-running queries.

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