DBCC DROPCLEANBUFFERS

Question

DBCC DROPCLEANBUFFERS

f_ernestog

Ten Centuries

Points: 1187
More actions
March 19, 2010 at 2:18 pm

#212843

Hi, I was wondering why you need to run a checkpoint before having to run this command, I know that the checkpoint will write the dirty pages to the disk, and this one will clean up the data cache, but I wonder why I should make sure the pages are written to disk, since even if they are not, there will be a record of the change in the T-LOG, or is it that checkpoint won't go to the log to write the pages (this is what I think actually)
Frank.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Where did you get the idea that you have to checkpoint before running this? DBCC DROPCLEANBUFFERS clears out the clean buffers. Checkpoints flush out the dirty buffers so the two should not effect each other.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

f_ernestog Ten Centuries Points: 1187 More actions · Answer 2

http://msdn.microsoft.com/en-us/library/ms187762.aspx

Check in the remarks, the second paragraph, yo will see this: "To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache."

Maybe I am not getting it the right way 🙂

Frank.

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Very interesting...I have never read that before. When performance testing, I typically just issue the DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE commands.

It makes sense that dropping only the clean buffers (without issuing a CHECKPOINT) could leave open the chance that some of the dirty buffers contain pages that would get used in whatever queries you are testing and thereby skewing your results.

In looking further into this, I found a good article by the Grumpy DBA...check out this[/url] link.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

DropCleanBuffers only drops clean pages. ie ones that have not been modified and do not need to be written back to the data file. So if you have lots of dirty (modified) pages and run DROPCLEANBUFFERS, it won't drop all the data cache pages. It can't some of them are dirty.

To ensure that all data cache pages are clean, and hence will be dropped by DROPCLEANBUFFERS, run checkpoint. Checkpoint writes all dirty pages to dis and ensures that all the pages in the data cache are clean.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

f_ernestog Ten Centuries Points: 1187 More actions · Answer 5

Awesome!! thanks for your replies 🙂 now it's more clear for me and now I understand why. thanks guys!

Frank.