One of the things I feel I keep badgering on about in the world of SQL Server and query tuning is to be fair and consistent when comparing the different possible variants. If you don’t level the playing field, then potentially it will lead to you thinking a particular query out-performs another when in fact the opposite could be true. It’s a bit like comparing 2 paper boys to see which one can complete a given round the quickest - if one of them has been doing the round for the past 2 weeks but the other has never done that round before, then are you going to be able to reliably tell which one is actually quickest?
No.
One of them knows the route, the houses, and which houses have the dog that will chew their hand off given half the chance, but the other paper boy has no such knowledge. This puts him at an unfair disadvantage and almost certainly he will be slower. So until you compare them fair and square, from the same starting point, then you won’t truly know who is quicker.
This is true when query tuning. So how do I do fair comparisons of queries? Well, nothing ground-breaking:
Clear the data cache and execution plan cache.
The first time you run a query an execution plan will be generated and stored in the execution plan cache. The data will be held in SQL Server’s data cache/buffer. If you then run it for a second time, there is already an execution plan to reuse and the data is in the cache meaning it doesn’t need to be re-read from disk. This can give vast improvements in query performance.
CHECKPOINT -- force dirty pages in the buffer to be written to disk
DBCC DROPCLEANBUFFERS -- clear the data cache
DBCC FREEPROCCACHE -- clear the execution plan cache
So, if you don’t clear the cache down between tests, then it means you will be getting skewed results. A second different query may appear to be more performant, when in fact it’s benefiting unfairly from the primed cache created by the first query.
The data cache is usually the biggest contributor to the performance improvement. If you want to get an idea for what is in the data cache, here’s a basic query to run in your database that will show the number of cached data pages by table. This is a slightly adapted version of a query available here in MSDN (strips out the sys table figures) for SQL Server 2005 and above:
SELECT t.name AS TableName, COUNT(*) AS NumberOfCachedPages
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p ON
au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id AND au.type = 2
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
INNER JOIN sys.tables t ON obj.object_id = t.object_id
WHERE database_id = db_id()
GROUP BY t.name
ORDER BY NumberOfCachedPages DESC;
Running this after clearing the cache, and then after running a query, will soon give you at least some idea of what is being cached. I recommend having a look at sys.dm_os_buffer_descriptors.
So, this is why whenever I want to try out different versions of a query for performance, I like to follow a standard approach:
start a trace in SQL Profiler, filtered down to just my process (by user name)
clear the cache
run query #1
record the reads, writes, CPU and duration from profiler
repeat steps 2 to 4, at least once more
run query #1 again but without clearing the cache - this is because I like to see how the query runs with a hot/primed cache
repeat steps 2 to 6 for query #2
compare the stats
I find it good to try each query a number of times, to get an average performance. Note up until now I’ve been talking about performance in terms of raw query duration. I also monitor reads/writes/CPU as they are good to keep an eye on too. Where two queries run in about the same time, there may be a more sizeable difference in the other stats to help choose a winner. Of course, you may want to optimise for reads or CPU at the expense of overall speed, but generally it’s duration that is of the most interest.
So when I see questions asking “why does query X run faster than query Y?”, I feel it’s worth checking whether the tests have been done from a level playing field if it’s not stated. Usually the answer is yes, but on the occasion where they haven’t been done in a fair manner it’s well worth the gentle nudge about clearing the cache.