SQL Server DBCC Commands: DBCC DROPCLEANBUFFERS

SteveStedman Posted on May 15, 2023 Posted in Performance 3 Comments

DBCC DROPCLEANBUFFERS is a DBCC command in Microsoft SQL Server that can be used to remove clean buffers from the buffer pool, which is a region of memory used by the database engine to store data pages read from disk. Removing clean buffers from the buffer pool can be useful for testing the performance of a query or for simulating a cold cache scenario, where the data pages needed by the query are not in the buffer pool and must be read from disk.

To use DBCC DROPCLEANBUFFERS, you do not need to specify any arguments. Simply execute the command in a query window connected to the database you want to remove the clean buffers from.

Here is an example of how to use DBCC DROPCLEANBUFFERS to remove clean buffers from the buffer pool:

-- Remove clean buffers from the buffer pool in the "MyDatabase" database
DBCC DROPCLEANBUFFERS ('MyDatabase');

The output of DBCC DROPCLEANBUFFERS will be a message indicating whether the operation was successful or not. If the operation was successful, all clean buffers will be removed from the buffer pool.

It is important to note that DBCC DROPCLEANBUFFERS is an operation that can have a significant impact on the performance of the database server. It should only be used for testing and debugging purposes, and should not be used in production environments.

DBCC DROPCLEANBUFFERS is a useful tool for testing and debugging query performance in SQL Server, but it should be used with caution.

3 Comments on “SQL Server DBCC Commands: DBCC DROPCLEANBUFFERS”

charlie arehart says:

May 17, 2023 at 14:32

Steve, when you say this is “destructive”, I fear that could be misinterpreted. It certainly “can have significant impact on performance”, as it clears all data stored in the buffers (so that it would need to be reloaded, when needed). And people should certianly fear that in clearing buffers, subsequent requests which WOULD have been faster by USING buffered data could now be SLOWER (until those or other queries reloaded data into the buffers).

But saying it’s “destructive” and “should not be used in production” without clarification implies that people could “lose data” and would “never” want to use it prod. That’s not true, and you’re not saying that, right? Just stressing the need to be cautious, right?

Or is there indeed a different point of clarification you’d make about its “destructive” nature? Thanks.

Reply
- SteveStedman says:
  
  May 18, 2023 at 09:35
  
  Thanks Charlie, I appreciate the input.
  
  You are right, it is not actually destructive, but it can significantly impact performance. I may have been a bit extreme on my wording there.
  
  I have updated the post based on your feedback, keeping me honest here.
  
  -Steve Stedman
  
  Reply
Charlie Arehart says:

May 18, 2023 at 11:48

Wonderful, and thanks so much. :-)

Reply