Impact of CHECKPOINT On Memory – SQL in Sixty Seconds #084

Some concepts are so good in SQL Server, they keep on coming back to me one way or another way. After reading my recent blog posts about CHECKPOINTS lots of people wanted to see the demonstration of the same. In this SQL in Sixty Seconds video, I will show you the Impact of CHECKPOINT on Memory.

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)