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

Both commands can be “turned on” using Transact-SQL commands using Query Analyzer (7.0 and 2000) or by setting the appropriate connection properties of the current connection in Query Analyzer. In this article, we will use the Transact-SQL commands for illustration.

The SET STATISTICS IO and the SET STATISTICS TIME commands act like settings, turning on and off various reported information on the resources our queries use. By default, these settings are not turned on. So let’s look at an example of how to turn these commands on, and take a look at what information they provide.

To begin our example, bring up Query Analyzer and connect to a SQL Server. For this example, let’s use the perennial favorite, Northwind, so go ahead and make it your default database for this connection.

Next, run this query:

Assuming you haven’t altered this table, 2,155 rows will be returned. This is your typical result, which you have seen many times over in Query Analyzer.

Now, let’s run the same query, but this time, let’s run the SET STATISTICS IO and the SET STATISTICS TIME commands before we run the query. Keep in mind that these two commands “turn on” performance tuning data only for the existing connection. If you run one or both of these commands, then drop the connection and start another, then you will have to reissue these commands for the new connection. If you ever need to “turn off” these commands during the current connection, you can by substituting OFF for ON in the commands.

Before we begin our example, run these two commands (don’t run these on a production server) first. These two commands will clear out SQL Server’s data and procedure cache. This helps to ensure that we are starting from a level field. If you don’t do this every time before you run the following examples, then your results may not be consistent.

Next, enter and run the following Transact-SQL commands:

Once you have done this, then run this command again:

(If you run all of the above commands at the same time, then your output will look different than mine, and it will be harder to see what is happening.)

After you run these command, you will see new information in the results window that you did not see before. At the very top of the window, you will see something like:

After the above data, the rows resulting from the query will be displayed. At the very bottom of the 2,155 rows will be this additional new information displayed:

(The results you get may vary somewhat, but we will talk about this below as we explain everything you see above.)

From the top of the output:

From the bottom of the output:

At the top of the output you see what appears to be the same time measurement twice, but the first one most likely will have some CPU and elapsed time, and the second one most likely will not. What is this telling us?

“SQL Server parse and compile time” tells us how much CPU time and total time it took SQL Server to parse and compile the Transact-SQL statement, “SELECT * FROM [order details]”, and then put the query plan that was created into SQL Server’s procedure cache for use by SQL Server.

In my case, it took 10 ms. of CPU time, and a total time of 61 ms. of elapsed time. Your CPU time will probably vary because your server is different than mine, and your elapsed time will vary because both your server, and your server’s load, will be different than mine.

The second time you see “SQL Server parse and compile time,” what this is telling you is the amount of time it took for SQL Server to retrieve the plan from the procedure cache so that it can be executed. Generally, the times here will be 0 because of how quick this task is.

If you should rerun the Transact-SQL command, “SELECT * FROM [order details]” again without clearing the cache (as recommended above), most likely both instances of CPU and compile time will be 0, as the cached query plan will be reused, so no new compile time is required.

So how is this information useful to you when performance tuning? Well, not a whole lot, but I needed to explain what this information meant. You will be surprised that most DBAs won’t know what it means.

What is most of interest to us is the time information displayed at the bottom of the query’s output. Here is it is again:

What this tells you is how much CPU time was used to execute the query, and how long the query took to run. Of these two pieces of information, only the first is of much use. The CPU time is a relatively consistent measurement of the amount of CPU resources it takes for your query to run, and this is relatively independent of how busy your CPU is. Sure, you probably will see some variation in this number, but it won’t be significant, not as wildly changing as the elapsed time number. The elapsed time number is a measurement of how long the query took to execute (not counting the time for locks or reads). This number will jump around a lot because of the ever-changing load on your server.