DBCC DROPCLEANBUFFERS

I have used “dbcc dropcleanbuffers” numerous times before but never really understood what was happening. I used to assume that by running this command, all the reads would then be done from the physical disk.

The first time I saw DROPCLEANBUFFERS, I read it like DROP’n’CLEAN BUFFERS. According to Books Online, it should be DROP “CLEAN BUFFERS”:

Removes all clean buffers from the buffer pool.

So what is a clean buffer? A clean buffer is a data page in memory that is NOT MODIFIED.

Then what is a dirty buffer? According to Books Online:

Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk.

Now we know DBCC DROPCLEANBUFFERS gets rid of the clean buffers, but which command gets rid of the dirty buffers? The answer is CHECKPOINT. According to Books Online:

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.

Alright, that’s all the boring stuff out of the way, now let’s get on with the fun stuff. Let me demonstrate with a simple example.

Let’s create a test database and a test table:

create database test
go
use test
go
create table test (data int)
go

We also need to turn on the stats:

set statistics io on

We are almost ready, press Ctrl + T to output results to text, now we are ready to play:

select * from test

Nothing exists yet hence no reads.

image

Execute the script below to see some reads:

insert test values (1)
select * from test

A row is now in memory hence the logical read.

image

Let’s clear the buffers:

dbcc dropcleanbuffers
select * from test

As expected, the page in memory is dirty, dbcc had no effect, hence no physical reads.

image

Let’s flush the dirty page to disk:

checkpoint
select * from test

The page is now clean but still in memory, hence still no physical reads.

image

Try to clear the buffers again:

dbcc dropcleanbuffers
select * from test

This time the buffers are cleared for real hence the physical read.

image

–oo00oo–

For more info, please refer to the following blog post:
SQL Server: What is a COLD, DIRTY or CLEAN Buffer?