DBCC DropCleanBuffers

Being day ten of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC DROPCLEANBUFFERS.

Description:

DBCC DropCleanBuffers is great to use in a development or test environment to flush all of the clean data pages out of memory to use for performance testing purposes.  This can be combined with DBCC FreeProcCache to simulate the state of a freshly started SQL Server without having to do a restart of the SQL Server.

DBCC DropCleanBuffers can be used to test queries with a cold buffer cache, meaning, any data pages will need to be loaded from disk in order for a query to run.

DBCC DROPCLEANBUFFERS Syntax:

dbcc dropcleanbuffers [ WITH NO_INFOMSGS ]

Example:

The following example will drop all the clean buffers out of memory causing a reload from next time they are needed.

dbcc dropcleanbuffers;

You get a result of the following:

DBCC execution completed. If DBCC printed error messages, contact 
your system administrator.

Notes:

For more information see TSQL Wiki DBCC dropcleanbuffers.

DBCC Command month at SteveStedman.com is almost as much fun as a root canal.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!