How do I flush the MSSQL cache for performance testing?

When performance tuning, you often follow a cycle of changing the tSQL, running it in query analyzer, jotting down the new execution time...and repeating the cycle again and again to reduce the time.

Because MSSQL caches results, your performance timings can be inaccurate. For example, the first run does the work and takes say 35 seconds. All subsequent runs might take 1 second or less because they are not doing any work. The subsequent runs are simply fetching the previously cached results.

This script resolves the problem by flushing the cache. Each subsequent run after a cache flush forces MSSQL to do real work thereby ensuring accurate performance test measurements.


/* Clear MSSQL before running performance tests */

-- 1) Clear buffer cache;
--    write dirty pages to disk & clean buffers.
CHECKPOINT

-- 2) Remove clean buffers from buffer pool
DBCC DROPCLEANBUFFERS

-- 3) Remove elements from stored proc cache
DBCC FREEPROCCACHE