Takeaway: To get consistent behaviour from SQL Server, I share a set of statements I like to run when performing tuning experiments.
Inconsistent Behaviour From SQL Server?
I often have conversations where a colleague wants to understand why SQL Server performs faster in some cases and slower in other cases.
The conversation usually starts “Why does SQL Server perform faster when I…” (fill in the blank):
- … changed the join order of the query
- … added a transaction
- … updated statistics
- … added a comment
- … crossed my fingers
- … simply ran it again
What’s Going On?
It can actually seem like SQL Server performs differently based on its mood. Here are some reasons that can affect the duration of queries like the ones above
- You changed something insignificant in the query. What you may be doing is comparing the performance of a cached plan with a newly compiled plan. Examples 1 – 4 might fall under this scenario. If that’s the case, then you took a broken thing and gave it a good thump. This percussive maintenance may be good for broken jukeboxes, but maybe not for SQL Server.
- What about those last two? Say you hit F5 to execute a query in Management Studio, and wait a minute for your results. You immediately hit F5 again and watched the same query take fifteen seconds. Then I like to point out that maybe all that data is cached in memory.
In order to do tune queries effectively, we need consistent behaviour from SQL Server, if only to test theories and be able to rely on the results. SQL Server doesn’t seem to want to give us consistent behaviour…
So Is It Possible To Get Straight Answers?
But maybe we can get straight answers from SQL Server. Here’s a test framework that I like to use before all experiments when I want consistent behaviour:
-- Only do this on dev sql servers! CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET STATISTICS IO, TIME ON -- Ctrl+M in Management Studio to include actual query plan |
The first two statements are meant to clear SQL Server’s cache of data. Because of write ahead logging, SQL Server will write data changes to disk immediately, but may take its time writing data changes to disk. Executing CHECKPOINT makes SQL Server do that immediately. After the checkpoint there should be no dirty buffers. That’s why DBCC DROPCLEANBUFFERS will succeed in dropping all data from memory.
The DBCC FREEPROCCACHE command will remove all cached query plans.
These commands give SQL Server a fresh starting point. It makes it easier to compare behaviour of one query with the behaviour of another.
The SET STATISTICS IO, TIME ON and the Ctrl+M are there in order to retrieve better information about the performance of the query. Often CPU time, Logical IO, and the actual query plan are more useful when tuning queries than elapsed time.
By the way,
If I’m lucky enough to be working on SQL Server 2014, I include
This will make sys.dm_exec_query_profiles super useful for gathering information about a long-running query without having to wait for it to complete.
Comment by Michael J. Swart — October 27, 2014 @ 9:36 am
What if you have a situation where the baseline performance has the data cached 99 percent of the time? The method above always requires disk reads and may not represent the baseline for production performance. I’ve had a situation where after doing the above, a table scan performed better than a low cardinality index seek; however when cached the low cardinaity index (even with many more logical reads) performed significantly faster than the table scan.
Comment by GP — October 28, 2014 @ 7:48 am
Hi GP,
That’s a great question.
Yes, the method I describe does always require disk reads. The whole purpose of the framework is to get to a consistent starting point in order to compare the performance of two queries. This method achieves that by clearing the cache for both queries. But any method that guarantees a consistent starting point will work for this purpose.
If 99 percent of the time the cache is warm and you can tolerate the one time out of a hundred when the cache is cold, then that does change things a bit. Personally, I’ve never found myself in that situation where cached data makes such a significant difference to which query is optimal and that the difference can’t be tolerated. But everyone’s has different situations.
(Did you know that the query optimizer always assumes a cold cache when costing. I learned that tip from Paul White).
Comment by Michael J. Swart — October 28, 2014 @ 11:30 am