articles >> performance tuning >> Use SET STATISTICS IO and SET STATISTICS ...

Use SET STATISTICS IO and SET STATISTICS TIME to Help Tune Your SQL Server Queries

By : Brad McGehee Jul 31, 2002

This is not an article on how to tune queries (that subject would take a book), but an article on how to use the often overlooked Transact-SQL SET STATISTICS IO and the SET STATISTICS TIME commands to help us tune our queries.

On the surface, the goal of performance tuning a query seems simple enough. Essentially, we want our queries to run faster. Whether it is trimming a query that takes 10 minutes down to 1 minute, or trimming a 2 second query down to 1 second, our ultimate goal is to reduce how long it takes our queries to run.

Given the simplicity of our goal, how come it is often so hard to accomplish it? While there are many reasons why query performance tuning is difficult, this article focuses on one of these. And that is that query tuning takes place if a fluid environment that is often changing from second to second, making it hard to really know what exactly is going on.

So what do I mean by this? If you are like most people who are trying to performance tune your queries, you are either performing your work on a test server with test data or a copy of your live data, or a production server with live data. In either case, neither server is ever exactly the same from one test run of your query to another test run. Remember, SQL Server dynamically adjusts itself as the demands on its resources change from moment to moment. While query tuning on a test server will certainly be less dynamic than on a production server, virtually any SQL Server will present similar problems.

If you don’t believe it, run the same query over and over again on a busy server, and in most cases, the time it takes to run from execution to execution will vary. Sure, not always by a lot, but it will change enough that it makes query tuning more difficult than it should be. For example, as you are query tuning on a production server, you may notice that your original version runs in about 10 seconds. You review the query and notice that you can make a slight change to the query that should speed it up. When you run it, the query now takes 12 seconds.

What has happened? Was your idea wrong, or perhaps was the server experiencing a heavier load than usual at that very moment when you ran the new query, and this was the cause of the increase run time? While you can run your queries over and over again to get an average time, that is a lot of work. What you need is a more scientific way to compare the performance of one query version to another.

 

Measuring SQL Server Resources Are Key to Easing Query Tuning Problems
When you execute a query on SQL Server, various server resources are used. One of those resources is a specific amount of CPU time. If you run the same query over and over again--and assuming the database has not changed--the CPU resources needed to run the query will remain very close to the same from execution to execution (usually less than 500 milliseconds or less of variation from run to run). I am not talking about the amount of time it takes for a query to run from beginning to end, but the amount of CPU resources needed by the query to run. The amount of time that it takes a query to run to completion will vary based on how busy the server is.

Another resource that SQL Server needs is IO. Whenever you run a query, SQL Server must retrieve data from the data cache (logical reads), and if the data it needs is not in the data cache, then it must read it from disk (physical reads).

As must be obvious from this discussion, the more CPU and IO resources that a query needs to run, the slower the query will perform. So another way to describe the task of query tuning is that you want to rewrite your queries in such a way as that they use less CPU and IO resources. If you accomplish this, then the performance of your query will have improved.

If you think of query tuning in terms of reducing the server resources it uses, (rather than in terms of how long it takes for a query to run) the easier it is to measure whether or not the actions you take to tune your query are actually helping or hurting, especially on a busy server whose resource usage is changing all the time. Let’s find out how we can measure these resources so we can tell if our queries are using more or less resources as we tune them.