DBCC DROPCLEANBUFFERS needs to be preceded by a CHECKPOINT to remove all buffers


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.

image

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!

image

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

image

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:

image

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

4 thoughts on “DBCC DROPCLEANBUFFERS needs to be preceded by a CHECKPOINT to remove all buffers

  1. Chintak Chhapia

    Hi Nakul,

    Good to know this if it works this way..

    But this is may be beacuse of the insert you have made, as you have made insterts, buffers are not clean and May be because of this it may not be cleared…

    I have tried this on select statement and in my case its always removing the enteries….

    Like

    Reply
  2. Nakul Vachhrajani

    Absolutely correct, Chinak!

    In fact, even if an update is executed while trying to tune queries (as is the case in most collaborative development environments), the CHECKPOINT becomes necessary. I have therefore added another experiment to this post. Please let me know your thoughts on the same.

    In short, if there are any operations which change the datapage, then the CHECKPOINT becomes necessary.

    Like

    Reply
  3. Chintak Chhapia

    Nakul,

    Also, to view whether buffer is clean or not, is_modified fields of sys.dm_os_buffer_descriptors as below

    select so.name,obd.is_modified,*
    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

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

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