DBCC DropCleanBuffers and Checkpoint in SQL Server

DBCC DropCleanBuffers is very useful T-SQL commands while doing the performance tuning of the queries. Use DBCC DropCleanBuffers to test queries with a cold buffer cache without shutting down and restarting the server. To execute these commands, user should have membership in the sysadmin fixed server role.

MSDN: To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DropCleanBuffers command to remove all buffers from the buffer pool.

Note: Do not attempt to execute DBCC DropCleanBuffers on a live SQL Server instance. Whilst DBCC DropCleanBuffers is a necessary [first] step in query performance tuning it should NOT be run on a live SQL Server instance which causes the index and data pages flushed from the cache and thus making the next round of DML statements run slower. Never run any of those 3 statements on a production server unless you know exactly what you are doing!

DBCC DropCleanBuffers: This command (when run) leaves behind only the dirty pages, which is actually a small portion of data. It removes all the clean pages for an entire server. Running this command will result in mostly empty buffer cache. Running any query after executing the DBCC DropCleanBuffers command, will use physical reads to bring back the data into the cache, which is very likely going to be a lot slower than memory.

This can be a useful development tool because we can run a query in a performance testing environment over and over without any changes in speed/efficiency due to caching of data in memory.

Checkpoint writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk. They will help us to produce the cold buffer cache.

Syntax: DBCC DROPCLEANBUFFERS [WITH NO_INFOMSGS] Arguments WITH NO_INFOMSGS: Suppresses all informational messages. Result Sets DBCC DropCleanBuffers returns: DBCC execution completed. If DBCC printed error messages, contact your system administrator. How to use this command -- Clear Buffer pool CHECKPOINT GO DBCC DROPCLEANBUFFERS GO

Conclusion

We should always test our query performance with the test environment in as close a state as possible to our production environment. DBCC DropCleanBuffers clears clean (unmodified) pages from the buffer pool Precede that with a Checkpoint to flush any dirty pages to disk first. It establishes a fresh database, but not a fresh system: If we come in the morning, drop buffers, re-execute query we will find the 2nd execution much faster than the first which is due to the cache in the bust system which clearly is not affected by dropping database buffers.