What is Clean Buffer in DBCC DROPCLEANBUFFERS? – Interview Question of the Week #215

Question: What is Clean Buffer in DBCC DROPCLEANBUFFERS?

Answer: I must give a big credit to the guy who actually asked me this question as in my over 20 years of the journey no one has ever asked me this question. Though the answer to this question is very complex let me try to answer this in the simplest words.

Let us first understand what is a buffer. A buffer temporarily stores data while the data is the process of moving from one place to another. It usually clears up when any system restarts as it is just temporary storage.

The data, which is in the buffer but not yet moved (or hardened) to the hard disk is called dirty data and the buffer is called a dirty buffer. There are many other names for this the buffer but I prefer to call it dirty buffer. In SQL Server we have a command CHECKPOINT which actually does a very important task. Whenever CHECKPOINT is executed it takes the data from the buffer and moves to the hard disk. This means it takes a dirty buffer and makes them clean as the data which it contains now is on the hard drive (and assumed safe).

So essentially, the buffers which contain the data moved already moved to hard drives are called Clean Buffer.

Now the data which is already in the clean buffer still helps SQL Server Optimizer by staying in the cache. The reason why we DBCC DROPCLEANBUFFERS to drop clean buffers is to remove them from the temporary storage so it does not help SQL Server Optimizer.

Well, that’s it. I tried my best to explain this in very simple words. If you have a more technical detailed answer, do not hesitate to post it in the comment sections. More information is always welcome.

Reference: Pinal Dave (https://blog.sqlauthority.com)