11. January 2010 04:22
When testing the performance of SQL Scripts it is important to make sure that every time your run something you are starting from scratch. It is deceptively easy to make a change to a SQL Script and believe that it has dramatically improved performance, when in reality you are simply reusing the execution plan or data that is stored in cache. For almost every situation it is best to test your scripts against the worst case scenario, which means no cached execution plans or data. To ensure that any improvements I make are really because of improvements I made, I like to drop these lines before any test scripts I am running:
-- Your SQL begins here
Be sure that you use these carefully as it will affect the whole server.
CHECKPOINT writes all dirty pages to disk.
DBCC DROPCLEANBUFFERS will ensure that you are testing against a cold cache which is going to be your worst-case scenario when running a query.
DBCC FREEPROCCACHE will clear out your execution plan cache if you are testing against a stored procedure. You can have a little more control over what is removed from the execution plan cache by passing the appropriate parameters to DBCC FREEPROCCACHE. More information on that can be found at MSDN.