DBCC DROPCLEANBUFFERS

Question

DBCC DROPCLEANBUFFERS

craighenderson

SSCommitted

Points: 1574
More actions
November 26, 2009 at 3:59 am

#156452

Can someone give me a little run down on this command
DBCC DROPCLEANBUFFERS
I want to run it against my server but I'm unsure of the potential risk if there even are any?
Thanks
Craig H

Viewing 12 posts - 1 through 11 (of 11 total)

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

Atul DBA Hall of Fame Points: 3610 More actions · Answer 1

DBCC DROPCLEANBUFFERS is used 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.

[CHECKPOINT] Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.

The end result is that you're starting with nothing cached. There is no side affect of this command as such.

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 2

Use for performance testing on dev servers, checkpoint the db before running. Don't run against live.

What it does is clear down your procedure cache - if you run against live you're going to see your box hammered until your most commonly used queries have all been recompiled and added into cache again

craighenderson SSCommitted Points: 1574 More actions · Answer 3

Andrew Gothard-467944 (11/26/2009)
if you run against live you're going to see your box hammered until your most commonly used queries have all been recompiled and added into cache again

This is what I was worried about, I currently have 25Gb in my buffer pool for my live DB and I was wondering if clearing this would make my problem worse before helping it (if at all)

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

Do not run this against a production server without a really good reason. You say you want to run it. Why?

What this does is go through the data cache and discard all clean (unmodified) data pages. That means, the next time a query runs, SLQ will first have to go to disk to get the data. That's far slower than memory. Do this on a busy production server and you can pretty much grind the app to a halt until the data buffer's populated again.

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

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

Andrew Gothard-467944 (11/26/2009)
What it does is clear down your procedure cache - if you run against live you're going to see your box hammered until your most commonly used queries have all been recompiled and added into cache again

That's DBCC FREEPROCCACHE

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

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

craighenderson (11/26/2009)
This is what I was worried about, I currently have 25Gb in my buffer pool for my live DB and I was wondering if clearing this would make my problem worse before helping it (if at all)

Depending what the problem it, it'll make things worse and not fix anything. Why is 25 GB of buffer pool a concern and what's the actual problem that you're having?

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

craighenderson SSCommitted Points: 1574 More actions · Answer 7

Hi thanks for confirming my fears over this

My problem is in this post

http://www.sqlservercentral.com/Forums/Topic824404-146-1.aspx

craighenderson SSCommitted Points: 1574 More actions · Answer 8

This is maybe a silly question, but does a reboot do the same job as clearing the buffers?

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

DropCleanBuffers drops everything from one of SQL's memory areas - the data cache. If you just run that you leave the procedure cache intact, the system caches intact, few other things.

Reboot wipes everything out.

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

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 10

GilaMonster (11/26/2009)
Andrew Gothard-467944 (11/26/2009)
What it does is clear down your procedure cache - if you run against live you're going to see your box hammered until your most commonly used queries have all been recompiled and added into cache again
That's DBCC FREEPROCCACHE

Argh - of course it is. What am I on about?

<sigh>

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 11

Andrew Gothard-467944 (11/26/2009)
Use for performance testing on dev servers, checkpoint the db before running. Don't run against live.
What it does is clear down your procedure cache - if you run against live you're going to see your box hammered until your most commonly used queries have all been recompiled and added into cache again

I think you are thinking of DBCC FREEPROCCACHE.

Random Technical Stuff[/url]