Capturing High Duration Queries Using SQL Server Profiler
by Joseph Sack

This is a two part excerpt from Joseph Sack's book, SQL Server 2005 T-SQL Recipes (Apress).  This book is considered to be a "no-fluff" desk reference that covers basic T-SQL data manipulation, the use of stored procedures, triggers and UDFs, and advanced T-SQL techniques for database security and maintenance. It also provides "how-to" answers to common SQL Server T-SQL questions, conceptual overviews, and highlights of new features introduced in SQL Server 2005.

There are usually two branches of query performance tuning: proactive and reactive. Proactive query tuning usually occurs during development. You design the database, populate it with data, and then start building queries. You build the queries based on application and end-user requirements. For those queries that don’t perform well, you can tune them before deploying out in the stored procedure or to the application developer.

Reactive performance involves capturing poor performance after the code has already been deployed to production. Data changes over time, and so does the effectiveness of indexes and the queries that use them. Queries that once performed well may execute longer than they did originally. You may hear complaints from end-users, or you might actually seek out poorly performing queries yourself.

One of the most valuable graphical interface tools in the SQL Server 2005 toolset is SQL Server Profiler. With SQL Server Profiler, you can monitor query activity as it occurs against your SQL Server instance. There are many potential uses for this tool, but this recipe specifically demonstrates how to use SQL Server Profiler to capture high duration queries.

1. The recipe begins by going to Start, Programs➤Microsoft SQL Server 2005➤Performance Tools➤SQL Server Profiler.
2. Once in SQL Server Profiler, go to File➤New Trace. This brings up the Connect to Server dialog box (see Figure 28-1). It is here that you select the name of the SQL Server instance to connect to, and the authentication method (either Windows or SQL). When you’re finished designating these values, click the Connect button.

   

   Figure 28-1. The Connect to Server dialog box

3. This brings up the Trace Properties dialog box (see Figure 28-2). In the Trace name field, type in Queries with a Duration > 5 seconds. SQL Server Profiler comes with a set of various trace templates. These templates contain pre-defined event selections, which allow you to get started with monitoring specific types of SQL Server instance activity. In this recipe, the TSQL_Duration template is selected from the Use the template field:

   

   Figure 28-2. The Trace Properties dialog box

4. Next, click the Events Selection tab in the Trace Properties dialog box (see Figure 28-3). It is here that the traced events from the pre-canned TSQL_Duration template are loaded. Currently two different events will be monitored, RPC:Completed and SQL:BatchCompleted. This means that SQL Server Profiler will return a row for every remote procedure call or Transact-SQL statement that has completed on the SQL Server instance. This window also shows the columns which will be returned in the trace data, in this case including the Duration (duration of the completed query), TextData (capturing the actual Transact-SQL), SPID (the server process id of the caller), and BinaryData (returns different data depending on the event).

   

   Figure 28-3. Events Selection

5. To see some other events and columns that can be added to the trace, click the Show all events and Show all columns checkboxes (see Figure 28-4). This adds several Event categories which can be expanded or contracted to show the events classes within. Available columns that are associated to the individual events can be checked or unchecked accordingly:

   

   Figure 28-4. Expanded events and columns

6. Next, you’ll want to configure this trace to only show events where the duration of the query is longer than five seconds. To do this, click the Column Filters button in the Events Selection window. This brings up the Edit Filter dialog box (see Figure 28-5). To filter query duration, click the Duration column in the left list box. In the right list box, expand the Greater than or equal filter and type in 5001 (this is a time measurement in milliseconds). To finish, select OK.

   

   Figure 28-5. Edit Filter dialog box

7. To kick off the trace, click Run in the Trace Properties dialog box.
8. In SQL Server Management Studio, within a Query Editor, the following query is executed:

   SELECT CustomerID, ContactID, Demographics, ModifiedDate
   FROM Sales.Individual

9. Switching back to the SQL Server Profiler, you can see that the query was indeed captured in the trace (see Figure 28-6). In order to stop the trace, go to the File menu and select Stop Trace. By highlighting the SQL:BatchCompleted row, you can see the full SELECT statement in the lower pane. The duration shows that the query took 7.8 seconds, and originated from server process ID 52.

   

   Figure 28-6. SQL Server Profiler results

How It Works

In this recipe, I demonstrated using SQL Server Profiler to identify a long running query. I filtered the list based on the query’s duration in this example, but you can also add additional filters based on your needs. For example, you can add an additional filter to only include activity for the AdventureWorks database. Or, you could add filters to only include queries that reference a specific database object or column name. Once you’ve captured the activity you are looking for, you can save the trace output to a file or table. You can also launch the Database Engine Tuning Advisor to evaluate the trace data for potential index improvements.

Joe Sack is a senior consultant with Microsoft's Consulting for IT Operations team. He is the author of SQL Server 2000 Fast Answers (Apress), and the recently published SQL Server 2005 T-SQL Recipes (Apress).