DBCC DROPCLEANBUFFERS is a very familiar DBCC command in use during performance tuning exercises. We have always used it to test queries with a cold buffer cache without shutting down and restarting the server. But, did you know that DBCC DROPCLEANBUFFERS alone may not be sufficient? Did you know that it is important to ensure that the transaction log is “cleared” before cleaning the buffers?

I learnt it from the Books On-Line here, did not quite believe it and decided to do my own little test, which I will demonstrate today.

Edit (October 03, 2011, 14:00 US-EDT): Please do not forget to refer an alternate experiment towards the end of the post. The additional experiment is based on the feedback received from my kind friend, Chintak Chhapia (blog).

Some important points…

Before we begin, I would like to mention a few important points:

1. If the first two lines seem alien to you, it is time to take a step back and understand the basics of SQL Server operation – especially transaction log and buffer pools
2. The queries and methods provided by this article should not be used in production environments. These queries and procedures are provided “as-is” and without warranty
3. Please do not use the queries and methods provided by this article without consultation and supervision by a DBA

Preparing the test

First of all, let’s create a sample database, and then change the recovery model to SIMPLE. This would allow the transaction log to be cleared when we issue a CHECKPOINT, and we would not have to run a transaction log backup. Note that after changing the recovery model, it is recommended to take a full database backup. I assume that the server where we are creating this database also contains the AdventureWorks2008R2 database for creation of sample data.

USE master
GO
CREATE DATABASE CleanBufferTest
ON PRIMARY
   (NAME = 'CleanBufferTest_Data',
    FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.DENALICTP03MSSQLDATACleanBufferTest_Data.mdf',
    SIZE = 10,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10)
LOG ON (NAME = 'CleanBufferTest_Log',
        FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.DENALICTP03MSSQLDATACleanBufferTest_Log.ldf',
        SIZE = 10,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 10)
GO

ALTER DATABASE CleanBufferTest SET RECOVERY SIMPLE;
GO

Using only DBCC DROPCLEANBUFFERS

Let’s create a test table and select data from it.

USE CleanBufferTest
GO
--Insert data from the AdventureWorks2008R2 database
SELECT * INTO CleanBufferEmployees FROM AdventureWorks2008R2.HumanResources.Employee
--Select data from the newly created table
SELECT * FROM CleanBufferEmployees

Now, using the below mentioned DMV based query, let’s see what data pages are available in the buffer.

USE CleanBufferTest
GO
--DMV to view the buffer descriptors
select so.name,* 
from sys.dm_os_buffer_descriptors obd
INNER JOIN sys.allocation_units au ON obd.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions part ON au.container_id = part.hobt_id
INNER JOIN sys.objects so ON part.object_id = so.object_id
WHERE obd.database_id = DB_ID()
AND so.is_ms_shipped = 0

Check the results of the query mentioned above.

Run DBCC DROPCLEANBUFFERS and then re-run the DMV based buffer look-up query:

DBCC DROPCLEANBUFFERS

The following was the output in my case. As you can see, our buffers are NOT clean!

Finally, clean up the test bench.

DROP TABLE CleanBufferTest

The ideal method to clean buffers

Let’s re-run the above test, but with this time, we have the CHECKPOINT in between to flush the transaction log to disk.

USE CleanBufferTest
GO
--Insert data from the AdventureWorks2008R2 database
SELECT * INTO CleanBufferEmployees FROM AdventureWorks2008R2.HumanResources.Employee
--Select data from the newly created table
SELECT * FROM CleanBufferEmployees
GO

--Checkpoint and then DROPCLEANBUFFER
CHECKPOINT
DBCC DROPCLEANBUFFERS


USE CleanBufferTest
GO
--DMV to view the buffer descriptors
select so.name,* 
from sys.dm_os_buffer_descriptors obd
INNER JOIN sys.allocation_units au ON obd.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions part ON au.container_id = part.hobt_id
INNER JOIN sys.objects so ON part.object_id = so.object_id
WHERE obd.database_id = DB_ID()
AND so.is_ms_shipped = 0
GO

As you can see, if DBCC DROPCLEANBUFFERS are used with a CHECKPOINT, Microsoft SQL Server forces all dirty pages for the current database to be written to disk and then cleans the buffers – resulting in a truly cold buffer cache. This recommendation has been made on the Books On Line page – http://technet.microsoft.com/en-us/library/ms187762.aspx

Edit (Added – October 03, 2011, 14:00 US-EDT)

As Chintak Chhapia (blog) points out in his comment below, the CHECKPOINT was required because we had a dirty page due to the SELET…INTO operation which ultimately is an INSERT operation.

In a collaborative environment, one would probably end up tuning stored procedures that perform UPDATEs after a couple of SELECTs or when running the application and generating test data in parallel. In such cases too, you would end up with dirty pages in the buffers. Let’s run a simple UPDATE on the CleanBufferEmployees table and then run DBCC DROPCLEANBUFFERS.

USE CleanBufferTest
GO
UPDATE CleanBufferEmployees SET VacationHours += 1
GO

--Attempt to clean buffers
DBCC DROPCLEANBUFFERS
GO

When we run our query to look at the buffers, we encounter a couple of rows in the result set, indicating that the buffers are unclean, and therefore requires a CHECKPOINT before DBCC DROPCLEANBUFFERS:

Until we meet next time,

Be courteous. Drive responsibly.