As a consultant, I see many aspects of SQL Server hardware configurations and application software that can cause daily performance problems. But one of the challenges that DBAs face the most is determining which statements executed in the SQL Server instance most damaging impact on performance. I encounter this challenge so often, in fact, that I felt compelled to revisit this topic—if only to put your mind at ease and tell you that solving this problem is certainly not beyond your capabilities and can take much less time and energy to solve than you realize.
All in the Details
In several previous articles, I've detailed several techniques for capturing and processing this kind of information. There are essentially two methods you can use to collect the data and format it in such a way that you can make sense of it. The first method is using SQL Trace to collect the statements issued to the SQL Server instance and parsing it with something like the SQL Signature UDF (as in "Finding Your Top 10 SQL Server Queries," InstantDoc ID 100121) or using an external application, such as the RML utilities (as discussed in "Trace Reporting with RML Utilities," InstantDoc ID 100670). The second method is using the built-in DMVs, such as the sys.dm_exec_query_stats (as outlined in "Are Your SQL Server Statements Performing Well?" InstantDoc ID 97761 and "Quickly Find Your Worst-Performing T-SQL Statements," InstantDoc ID 100201).
Because those resources are already available, I won’t go into detail about how to collect or parse the data. Instead, I'll concentrate on what you should look for—and why you should look for it—to maximize the time and energy your spend on finding the statements to tune first.
Back to Basics
Regardless of the method you use to obtain the data, your goal is to find the statements that are affecting your performance the most. You might think that sounds like a difficult task, but it's much simpler than you might imagine. Although the DMV might give you a large selection of metrics, most people use a combination of just five metrics to start with. These metrics are the Number of Executions, Reads, Writes, CPU, and Duration.
For the purpose of this process, let’s ignore Writes for now. And although Duration can be a valuable piece of information, it can also be somewhat misleading. For example, if a lot of blocking is occurring, Duration can vary wildly for any given statement. So, if the duration of a particular statement is high, it could be due to blocking and might not require tuning at all. Duration is also usually a byproduct of high reads or CPU usage, and if we reduce those, we reduce Duration. So, let's set that metric aside, too.
A basic concept you must grasp is that any one of these metrics by itself doesn't reveal enough information to let you see its true impact. To get a clear determination of which statements are affecting your system the most, you really need a combination of executions and one other metric. Think about that for a moment: A single query that requires a million reads to finish is pretty expensive, right? But if the query is executed only once per hour, it might not even be close to the top in terms of overall resource utilization. However, another query that takes 20,000 reads per execution but runs 1,000 times an hour would probably be a more likely candidate for tuning first. Remember to always view metrics in combination so that you get a better overall perspective of what's affecting the system the most. That way, you get a clearer idea of what you need to tune first to make the most impact.
Key In on Your Metrics
Any statement executed inside SQL Server will consume some level and form of one or more physical resources to perform the work. These resources are mostly composed of Memory, CPU and I/O. Since these resources are always limited and must be shared by other statements being executed, you want to ensure that each statement utilizes as little as possible. Generally, the fewer resources utilized, the more performant the statement will be. The two metrics I like to focus on most are Reads and CPU. A read is an 8K page being read from either memory or disk. If you're using SQL Trace, then only Logical reads are reported, but if you use the DMV, you can choose from Logical or Physical reads depending on what you're trying to troubleshoot. CPU is the number of milliseconds of actual processor time that the query consumed during execution. Don’t confuse that with Duration, which is the overall time to completion and may include idle time.
For determining which statements utilize server resources the most, I prefer to first concentrate on Logical Reads. The more reads a statement does, generally the more overall work must be done to complete the task—in contrast with a comparable statement that has fewer reads. That's why I prefer to start my list by looking at statements that, in a given time period, issue the most reads in total when taking into account the number of executions times the individual (or average) reads per execution.
In reality, the way in which the pages are processed can play a significant role in how much work is necessary to process the pages. For example, if the statement uses a hash join, it might require much more CPU resources than a comparable statement with the same number of reads that uses a nested loop or merge join. For that reason, you must also consider CPU usage, and you might want to sort on this metric in much the same way. Then, take your top queries from each list to determine which ones you want to have in your final list.
No Excuses
By following these guidelines and utilizing the tools referenced here, you should be able to easily identify which statements you should tune first to make the most positive impact on your system. Don’t lose sight of the fact that, in many cases, 80 to 90 percent of performance problems are caused by just a handful of statements or types of statements. By identifying and addressing the worst ones first, you can often make a significant impact with just a small amount of time and effort. Now you don’t have an excuse when your boss asks you to find the poorly performing statements in your SQL Server instance. Of course, tuning the statements might be a different matter altogether!
