Measuring SQL Performance

By Christoffer Hedgate, 2005/12/23 (first published: 2004/04/02)

Time

The most simple way to measure performance is of course to measure the time it takes to execute a query. If you have not already noticed it, take a look in the status bar at the bottom-right corner of Query Analyzer. There you will find a timer that shows how many hours:minutes:seconds it took for a query (or rather the entire script) to execute. This is of course not a very exact measurement. Most queries you want to measure will probably not take more than a second to run, in a high-traffic environment they should probably execute in milliseconds if they are correctly optimized. So you need a better instrument to measure the execution time for a query.

Another, and better way to measure the amount of time it takes for a query to execute is to use the built-in function GETDATE(). Example 1 show how you can do this. The example uses the command WAITFOR to make the query execution 'stand still' for as long as we specify with DELAY. By first storing the present date and time when the execution begins and then comparing this to what it is when the execution is finished we can get a more exact measurement with milliseconds specified. Note however that the time is only specified down to 1/300 of a second (i.e. 3.33 ms). So, if a query takes 40 ms to execute that means somewhere between 40-43 ms.

-- Example 1

DECLARE @start datetime, @stop datetime

SET @start = GETDATE()

WAITFOR DELAY '00:00:00.080' -- do not do anything for 80 ms

SET @stop = GETDATE()

SELECT 'The execution took ' + CONVERT(varchar(10), DATEDIFF(ms, @start, @stop)) + ' ms to finish'

STATISTICS TIME

The best way to measure time however is to use the configuration setting SET STATISTICS TIME. The syntax for this is as shown below:

SET STATISTICS TIME {ON | OFF}

When this parameter is set to on the results pane of Query Analyzer will show statistics for the time it took to execute a query. Note that if you are running QA in grid mode you will need to switch to the Messages tab to see this. Example 2 demonstrates this:

-- Example 2

USE Northwind
GO

SET STATISTICS TIME ON 
SELECT * FROM orders

In my results pane I get the following text:

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

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

(830 row(s) affected) 

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

At first glance this might seem complicated to understand, but more or less the only thing you need to do is to look for the row with SQL Server Execution Times that is printed right after the text that specifies the number of affected rows. Above this you can see the time it took to parse and compile the query, but that time is not what we are interested in here. Most of the times this will be 0 ms if you run the same query several times in a row since the execution plan will already be cached. As said earlier, what we are looking for is the time it took to execute the query. In the example above it needed 30 ms of CPU time, but the total amount of time needed was 500 ms (try replacing the WAITFOR statement in example 1 with the select statement in example 2 and see if GETDATE gives you the same measurement). But if CPU time was only 30 ms, then where are the remaining 470 ms? The answer for this is I/O.

STATISTICS IO

As you probably know I/O is short for Input/Output. You could say that it means reading/writing resources, and normally you mean reading/writing from/to disk or memory. Very simply described, SQL Server needs to have the data pages containing the data to return to the client stored in memory (RAM). If they are not already cached there they must first be read from disk where they are physically stored and then placed in memory, from where they can then be returned to the client. The data pages will then be cached in memory for an unspecified time, which depending on several factors can range from 0 - ~ (indefinitely). Therefore a query might need more time to execute the first time you execute it, and because of this you should always execute the query a couple of times when measuring performance for it.

It is not only the time it takes for a query to execute that is interesting when measuring performance. Equally important (and often even more) is the amount of system resources that is needed to execute it. Since I/O is normally the slowest part of a query, especially if physical disk access is needed, it is very important to know the amount of I/O resources needed to execute it. The way to measure this is to use another configuration setting called SET STATISTICS IO. The syntax for this is similar to that of SET STATISTICS TIME:

SET STATISTICS IO {ON | OFF}

The result however is different. Again, look in the text of the results pane in QA. I executed example 2 a couple of times and the result is shown below:

Table 'Orders'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0.

First we have the table name. Then comes the number of time this table was scanned, or rather accessed, to fetch the result of the query. The next parts tells us how many pages (data and/or index) that were read from the cache in memory to fetch the results, how many pages that were read from disk and the final number called read-ahead reads shows how many pages were placed into the cache for the query. The numbers you should normally look at is logical and physical reads plus scan count, and they should all of course be as low as possible. It might be better to have 100 logical reads than 10 physical reads since it is faster to read from memory, but generally speaking they should both be as low as possible. If you execute a query a couple of times physical reads will often be 0 since the data pages will already be cached after the first execution. Use these numbers to compare the resources needed when executing the same query formulated in different ways.

Other tools

With the above mentioned tools you have a good way of deciding which of several different versions of a query you should use to get the results you want in an optimal way. There are lots of other tools available as well, but I will not discuss them in this article. If you want to experiment with them yourself I would recommend you take a look at the following tools:

• Show execution plan: by pressing Ctrl-K you get an extra tab when executing queries in QA. This tab shows a graphical representation of the execution plan used by SQL Server to execute your query. There is lots of information in Books Online about how to use the information shown there, as well as articles online.
• SET STATISTICS PROFILE: This configuration option gives you a textbased variant of the execution plan.
• SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT: These options both present information regarding the resources and execution plan that would be used to execute the query, without actually executing it.
• Profiler, Sysmon (Performance Monitor) and other external applications: Finally there are several external applications that can be used to measure and show different events and measurements in SQL Server and the system under execution. Profiler, a tool in the SQL Server client tools pack, connects to SQL Server and log all kind of diffenent events that occur, and Sysmon can of course be used to log measurements for a huge amount of performance counters both for SQL Server and the system as a whole.