One thing

that often amazes me is that many SQL Server developers do not actually measure

the performance of their queries. When you are working with a small site or home

project you might not see a big difference, but when implementing systems with

large amounts of users and high levels of traffic you can not just settle with

the fact that your query returns the expected result. You must also make sure

that your queries use the least amount of resources and execute as quickly as

possible. Sure, you can read articles and literature that describe how to write

queries that perform well, but you can still not be sure that they work in an

optimal way for your specific situation. After all, different schema designs,

amounts of data, hardware resources etc all affect how a query performs. And one

of the problems with SQL is that you can write the same query (i.e. that return

the same results) in many different ways, and the performance of these different

formulations will often differ as well. When I started investigating why some

developers did not compare the performance of their queries it became clear to

me that the main reason is that they do not know how to do this in an easy way.

Many of them thought that you needed external tools, more or less complicated,

to run against your server, and they did not have the time or inclination to

learn and try these. This article will describe a couple of much easier methods

of measuring performance of queries.

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.