Using Profiler to Identify Poorly Performing Queries

Identifying Long Running Queries is First Step

At this step in the SQL Server performance audit, you should have identified all the "easy" performance fixes. Now it is time to get your hands a little dirtier and identify queries (including stored procedures) than run longer than they should, and use up more than their fare share of SQL Server resources.

Slow running queries are ones that take too long to run. So how long is too long? That is a decision you have to make. Generally speaking, I use a cutoff of 5 seconds. In other words, any query running 5 seconds or less is generally fast enough, while queries that take longer than 5 seconds to run are long running. This is an arbitrary decision you have to make. In the company where I work, the report writers, who are the ones who write most of the queries that are run against our databases have a different standard than I have. They only consider a query to be long running if it takes more than 30 seconds to run. So, one of your first steps is to determine what you think a long running query is, and then use this as your standard during this portion of the performance audit.

We don't have unlimited time to tune queries. All we can do is to identify those queries that need the most work, and then work on them. And if we do have time, then we can focus on those queries that are less critical (but still troublesome) to the overall performance of our SQL Servers. Also keep in mind that sometimes, no matter how hard you try to tune a particular query, that there may be little or nothing you can do to improve the performance of a particular query.

Before You Begin

For this part of the performance audit, you will be using the SQL Profiler tool that comes with SQL Server. As this article focuses on how to perform a performance audit, and not on how to use tools, it is assumed that you know how to use SQL Profiler. If you have not used it before, check out the SQL Server Books Online to get you started on the basics of how to use it.

Before you begin using Profiler to capture the query activity in your SQL Servers, keep the following in mind:

• Don’t run the Profiler on the same server you are monitoring, this can noticeably, negatively affect the server’s performance. Instead, run it on another server or workstation, and collect the data there.
   

• When running the Profiler, do not select more data than you need to collect. The more you collect, the more resources are used to collect them, slowing down performance. Only select those events and data columns you really need. I will make recommendation on exactly what to collect shortly.
   

• Collect data over a “typical” production time, say over a typical 3-4 hour production period. This may vary, depending on how busy your server is. If you don’t have a “typical” production time, you may have to collect data over several different periods of a typical production day to get all the data you need.

When you use Profiler, you have two options of how to "set it up." You can choose to use the GUI Profiler interface, or if you like, you can use the built-in Profiler system stored procedures. While using the GUI is somewhat easier, using the stored procedures to collect the data incurs slightly less overhead. In this article, we will be using the GUI interface.

What Data to Collect

Profiler allows you to specify which events you want to capture and which data columns from those event to capture. In addition, you can use filters to reduce the incoming data to only what you need for this specific analysis. Here's what I recommend:

Events to Capture

• Stored Procedures--RPC:Completed

• TSQL--SQL:BatchCompleted

You may be surprised that only two different events need to be captured: one for capturing stored procedures and one for capturing all other Transact-SQL queries.

Data Columns to Capture

• Duration (data needs to be grouped by duration)

• Event Class

• DatabaseID (If you have more than one database on the server)

• TextData

• CPU

• Writes

• Reads

• StartTime  (optional)

• EndTime  (optional)

• ApplicationName (optional)

• NTUserName (optional)

• LoginName  (optional)

• SPID

The data you want to actually capture and view includes some that are very important to you, especially duration and TextData; and some that are not so important, but can be useful, such as ApplicationName or NTUserName.