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

SET STATISTICS IO and SET STATISTICS TIME to the Rescue
Both the SET STATISTICS IO and the SET STATISTICS TIME Transact-SQL commands have been around a long time. But for some reason, they are often ignored by many DBAs when it comes time to performance tuning queries. Perhaps it is because they aren’t very “sexy”. But whatever the reason, they are still handy, as we will see.

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:

SELECT * FROM [order details]

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.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE


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

SET STATISTICS IO ON
SET STATISTICS TIME ON


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

SELECT * FROM [order details]

(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:

SQL Server parse and compile time: 
CPU time = 10 ms, elapsed time = 61 ms.

SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.


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:

Table ‘Order Details’. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.

SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 387 ms.


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

So what does all of this mean?

SET STATISTICS TIME Results
The SET STATISTICS TIME command is used to measure a variety of times, some of which may or may not be of much interest to you when performance tuning queries. This command is responsible for the following information displayed on your screen:

From the top of the output:

SQL Server parse and compile time: 
CPU time = 10 ms, elapsed time = 61 ms.

SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.


From the bottom of the output:

SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 387 ms.


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:

SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 387 ms.


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.

Because the CPU time is relatively consistent, you can use it as a way to help you determine whether the changes you make in your queries, during performance tuning, are actually helping or hurting. More on this later.

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

One Response to “Use SET STATISTICS IO and SET STATISTICS TIME to Help Tune Your SQL Server Queries”

  1. Eric in Sacramento Reply December 13, 2012 at 8:27 pm

    Excellent article! Very well-written, clear, and simple to understand. The best explanations I’ve seen on generating and interpreting statistics so far.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |