Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


How to Clear the Buffer Pool
Published: Dec 02, 2020
Since my last post I’ve had a question emailed to me which I just wanted to take a second to clarify as it’s a very good question and one that, I imagine, a lot of people have had or might not fully realise.

Everyone I speak to these days seem to know that it’s best practice to clear the buffer pool before running tests on query performance, and that to do so we run the following:

dbcc dropcleanbuffers
go


HOWEVER, I rarely run this on its own because it doesn’t necessarily clear the buffer pool…

Basically the clue is in the name… it drops the “CLEAN” buffers… what that means internally to SQL Server is that it will eject all pages from the buffer pool which have not been modified.

Any modified pages are marked as Dirty pages and therefore are not removed by this command. Dirty pages are only written out to disk when a CHECKPOINT is run against the database. A CHECKPOINT is run automatically in the background of SQL Server and is, generally speaking, not something you need to mess with (although it is possible in some rare cases that you might need to intervene).

This is not an internals post on Checkpoint (although I may make a note to do one of those soon)… so I’ll not go into detail but, suffice it to say, there are plenty of times when you’ll find dirty pages in the buffer pool which have not yet been flushed out to disk. These will NOT be cleaned out by the DROPCLEANBUFFERS command.

Hence, in my examples, I tend to lead with CHECKPOINT in order to run a manual checkpoint against the database… this flushes all dirty pages to disk, marking them as clean, and therefore only leaves clean pages in the buffer pool… therefore the very next command of DROPCLEANBUFFERS will eject ALL pages from the buffer pool, leaving you with a completely clean slate with which to test your queries.

Hope this makes sense and answers any questions you may have had.

checkpoint
dbcc
dropcleanbuffers
go


Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.