Clean Your Buffers for Accurate Performance Testing

In order to do accurate performance testing between multiple runs of a SQL Server command or scripts you need to remember to clean the buffer, procedure and system cache between each test run.  Without cleaning the buffer and caches between runs, your test query may run faster because some or all of the data needed is found in memory.  To accomplish cleaning/clearing the buffers and caches you will need to run a number of different commands.

The first thing you want to do is to flush all the dirty pages from the buffer cache.  Dirty pages are those pages that have been updated in the buffer pool but have not been written to disk.  To get these dirty pages out of the buffer pool you just need to run a CHECKPOINT command.  By running the CHECKPOINT command all dirty pages for the current database will be written to disk. 

The second thing is to remove all the pages from the buffer pool.  You want to do this so that each test run will not find any existing pages in the buffer pool.  By cleaning the buffer pool before each test run SQL Server will have to re-read the data it needs from disk.  To clean the buffer pool you execute the command: DBCC DROPCLEANBUFFERS. 

Next you should remove your execution plans from the procedure cache.  To remove all the plans from the procedure cache you can run the “DBCC FREEPROCCACHE” statement.  Or you can pass parameters, like a plan handle to remove just a single cached plan.  I find it easiest to remove all cached plans, by just running the “DBCC FREEPROCCACHE” statement. Of course, I only do this in an isolated testing environment, because this removes all plan and cache information for all batches that are running, and not just my test queries.

Lastly you should consider clearing the system caches as well.  To accomplish this, you can run the “DBCC FREESYSTEMCACHE(‘ALL’)” statement.   Cleaning this cache, as well as all the other items discussed, will allow you to compare your execution performance between multiple runs of your TSQL command or scripts.

See all articles by Greg Larsen