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.
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.
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.