One of my the most frequent activity when playing with databases is running performance tests. It should be easy to understand because choosing query A or query B rarely is an obvious decision without running some tests. When it comes to comparing performance, there are two extremely helpful statements - DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS. They make sequential tests more isolated when it comes to caching.

Buffers

When a particular data block is read from disk to execute some SQL query, SQL Server keeps it in the memory for some time in case it is needed again. Moreover, UPDATE statements not necessarily cause updating data on disk, usually Transaction Log gets a proper entry (which can also be deferred) and new data is kept in a part of memory called data buffer. By doing so SQL Server caches data to reduce a number of physical reads and writes to disk which are relatively slow. It substantially helps enhancing performance. But it is not that great if you want to test performance of an isolated SQL query.

Database buffers can be easily cleaned by running the following statements:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

The first one flushes data from the buffers to disk and marks the buffers as clean. The second one removes clean buffers. The next execution of any SQL query that requires data from database will do physical reads.

Plan cache

Before executing any query, SQL Server has to generate and choose an execution plan. As a single complex query can have multiple possible plans, choosing the best one is not an easy or a quick task. It may take time. For optimization purposes caching it makes perfect sense. Of course, when testing performance, I do not want this to happen so I clean this cache using the following DBCC option:

DBCC FREEPROCCACHE

It can have more arguments specified. For example, only a specific plan can be removed from the cache. The statement without additional arguments drops all plans.