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.

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

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)

SQL Cache, SQL Memory, SQL Scripts, SQL Server, SQL Server DBCC
Previous Post
How to Extract Alphanumeric Only From A String? – Interview Question of the Week #214
Next Post
How to Know When Memory Constraints are Negatively Impacting CPU in SQL Server? – Interview Question of the Week #216

Related Posts

4 Comments. Leave new

  • TechnoCaveman
    June 23, 2019 7:43 pm

    Very good. Clean buffers still help SQL for frequently used data.
    Is a data buffer 64 K in size ?
    Are indexes kept in the same buffer space with data ? If so, then dropping clean buffers would “free up memory” but drop index and data buffers.
    Thanks in advance for any answer

    Reply
  • As my point of view.instead of restart SQL service. We will use drop cleanbuffers in non production hours.I am a DBA for restarting SQL service I want to get manager approval.instead of this I can use drop cleanbuffers. Without restart ram utilization reduce in SQL server kindly explain sir.

    Reply
  • Yogesh Shinde
    August 6, 2020 8:59 am

    Are Dirty Page and Dirty Buffer same?

    Reply
  • Hi.. is entire dirty page moved to disk when checkpoint process runs or is it copied to disk?

    Reply

Leave a Reply