November 26, 2009 at 3:59 am
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
November 26, 2009 at 4:05 am
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.
November 26, 2009 at 4:08 am
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
November 26, 2009 at 4:11 am
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)
November 26, 2009 at 4:12 am
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
November 26, 2009 at 4:13 am
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
November 26, 2009 at 4:15 am
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
November 26, 2009 at 4:18 am
Hi thanks for confirming my fears over this
My problem is in this post
http://www.sqlservercentral.com/Forums/Topic824404-146-1.aspx
November 26, 2009 at 4:36 am
This is maybe a silly question, but does a reboot do the same job as clearing the buffers?
November 26, 2009 at 4:41 am
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
November 27, 2009 at 4:16 am
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 againThat's DBCC FREEPROCCACHE
Argh - of course it is. What am I on about?
<sigh>
November 28, 2009 at 12:26 am
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.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply