SQL Server Query Performance Audit Checklist
SQL Server Job Checklist
Have you identified all long running queries?
Have you prioritized the queries?
Have you reviewed the execution plans of the above prioritized queries?
Enter your results in the table above.
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:
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
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
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.
Filters to Use
Filters are used to reduce the amount of data collected, and the more filters you use, the more data you can filter out that is not important. Generally, I use three filters, but others can be used, as appropriate to your situation. And of these, the most important is duration. I only want to collect information on those that have enough duration to be of importance to me, as we have already discussed.