We are going to get right to it and not bury the lead here, query tuning is not the same thing as performance tuning. Now there are certainly many places and many people who use the terms interchangeably, but they are not the same.
The Difference Between Performance Tuning and Query Tuning
Query tuning is, in fact, a subset of performance tuning, a subset we should certainly be paying attention to, but there is more to performance tuning.
For purposes of this post, we will define the two categories of tuning as follows:
- Query tuning – the focused process of improving the performance, in terms of both duration and resource utilization, of a single query
- Performance tuning – the focused process of improving the duration and resource utilization of all queries, regardless of database, which run against the instance.
Simply by reading the two definitions you can already being to see where the difference occurs, it is simply in the scope of the effort. However, you can also clearly see why query tuning is a subset of performance tuning as cleaning up a particularly nasty query can certainly improve the performance of the instances as an entire entity.
It is not unusual for the process of query tuning to lead us to discover that we need to spend some time performance tuning the entire instance, nor is it unusual for a performance tuning effort to lead us to a specific query or couple of queries.
While we should be cognizant that the two terms are not interchangeable, we should not treat them as if they are wholly isolated from each other. To be fair, there is an element of SQL Server administration, or at least a part of that element, which bridges the gap between performance and query tuning.
We when talk about index management that encompasses both adjusting existing indexes as well as creating new ones, not to mention cleaning up duplicate, overlapping, or unused indexes. All of these elements add something to the space “between” performance tuning and query tuning.
As we start to take a look at the specifics of tuning, we should remember that we are talking in generalities and concepts as talking in too much specificity is ineffective thanks largely to all of the variances which add up to “it depends”. We are also going to skip the specific whys and wherefores that lead us to this specific tuning endeavor, other than generally setting some context.
First let’s turn our attention to query tuning. Suppose we have a select query with some joins and multiple filters in the predicate, in short a rather typical query. In order to investigate our issue we need the full text of the query, valid parameter values, and a test environment with a comparable data size.
It is perfectly fine if the server environment is slower than production. We are going to run the query against this test environment, setting STATISTICS IO and STATISTCS TIME on as well as capturing the query plan. Please remember we should be comparing query plans across servers/environments.
Once we have the query plan in hand we are going to look for warnings, such as for plan affecting implicit conversions, high cost operators and the like. Then we will look at both query structure and indexing looking for opportunities to improve.
We will, of course, iterate through this method until we have made improvement to the query. It may take a few hours or a few days to really define and resolve the problem or at least pass definitive guidance to a developer. This is a relatively generic example of query tuning.
Let us compare that to performance tuning. When we sit down to performance tune we are looking at a different set of metrics. In performance tuning we are going to use things like the “Performance Tuning With Waits and Queues” methodology, perhaps take a deep dive into performance counters so we can learn where the systemic bottle necks are.
- See also: A Guide to SQL Server Wait Monitoring
Additionally, we will look at queries in terms of duration, resource utilization, and frequency of execution so we can evaluate the impact of those queries on the system as a whole. Once the data is gathered, we may now be engaging storage admins, system admins, virtualization admins, perhaps even network admins to bring their expertise to bear on specific segments of our issue.
The data may also point us to look at configuration settings within the instance such as trace flags, TempDB, or cost threshold of parallelism, to name a few. By the time we go through engagements, approvals, changes, testing, quite possible more than once we can see it make take days to weeks to get through a particularly difficult situation.
We should also remember that solving one performance bottleneck has a reasonably good chance of leading us to another one, where the second one was not an issue because the first bottleneck was masking it, while that is possible in query tuning it is far less likely.
If you go back and read those two situations it starts to become quite clear that query tuning and performance tuning are not the same thing. At the same time it is somewhat easy to see how index tuning bridges the gap.
When we tune queries, we are absolutely looking at adjusting existing or creating new indexes to improve performance. At the same time adjusting indexes, adding new ones, and cleaning indexes up can be a win for tuning the performance of the entire instance.
Even in the relatively simple scenarios put forth in this post, you can easily see that query tuning is a subset of, rather than a synonym for, performance tuning. The difference in time and approach clearly differentiates them even as index management links them together.
If performance is your passion, you should certainly be query tuning and performance tuning, but understanding the difference in the terminology could certainly making finding answers or having conversations a whole lot less difficult.
Written by Peter Shore/posted by Zak Cole