Caveman's Blog

My commitment to learning.

SQL Server – Clean Buffers

with one comment


Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. 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. [1]

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

It is recommended these commands should not be executed in a production environment where the SQL Server caching helps in gaining performance. Running these commands could adversely impact the server performance.

Reference:
1. DBCC DROPCLEANBUFFERS (Transact-SQL)

Advertisements

Written by cavemansblog

December 10, 2012 at 11:34 pm

Posted in Uncategorized

Tagged with ,

One Response

Subscribe to comments with RSS.

  1. Do you mind if I quote a few of your articles as long as I provide credit and
    sources baxk to your weblog? My website is in the very same niche as yours and my
    users would really benefit from a lot of the information you present here.
    Please let me know if this okay with you. Cheers!

    mobile ins

    July 25, 2014 at 10:11 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: