To understand what actually CHECKPOINT and DBCC DROPCLEANBUFFERS do first we need to understand a couple of more concepts which are what is a clean buffer and dirty buffer in memory. I suggest you read the following blog post before continuing reading this blog post.
Dirty Pages – How to List Dirty Pages From Memory in SQL Server?
What is Clean Buffer in DBCC DROPCLEANBUFFERS?
Now, that you have read the blog post, I am confident that you know how Clean Pages and Dirty Pages work.
The next task is to get the script which displays dirty pages and clean pages in the buffer pool of the memory. Here is the blog post where I have written the blog post about that topic. How Dirty or Clean is SQL SERVER’s Memory?
Once you run the query on that page, you will get output similar to what you see in the video. Now is the time when you are ready to execute the keyword CHECKPOINT.
What this entire video over here:
If due to any reason, you are not able to watch the video, here is the blog post which will walk you through the entire story of the blog post with the images and scripts: SQL SERVER – Impact of CHECKPOINT and DBCC DROPCLEANBUFFERS on Memory.
Let me know what you think of this video. If you like similar videos, please leave a comment and I will build a few more SQL in the Sixty Seconds. Meanwhile, if you like to watch the longer video, I strongly suggest you watch my earlier video here where I discuss Indexes Reduces the Performance of SELECT statement.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Thank you for your demonstration, Pinal. I noticed, that after CHECKPOINT not only number of dirty pages become 0, but also number of clean pages reduced for the AdventureWorks2014 database. Why is it?
That could have been because sometimes there are many different operations are happening internally. I will create another blog post talking about clean buffer and post it on next Wednesday.
Hey Pinal, once again another great subject.
Regarding checkpoints, I’d like to ask how checkpoints affect the fail over time on Always On.
If you have an intensively updated database, can a checkpoint decrease the total time of a manual fail over?
Thanks for answering!