SQL SERVER – Impact of CHECKPOINT and DBCC DROPCLEANBUFFERS on Memory

I love working with my clients and solving their SQL Server Performance Tuning challenges which are difficult to solve. I have been working for over 10 years performing Comprehensive Database Performance Health Check at many different clients and my clients can vouch that I have seen pretty much every single performance problems and I can solve them relatively quickly. Today we will discuss the Impact of CHECKPOINT and DBCC DROPCLEANBUFFERS on Memory. We will see how each of them clears dirty and clean pages from the buffer memory pool.

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 both the blog posts, go to How Dirty or Clean is SQL SERVER’s Memory? and run the query displayed on the page. The query will give us a similar result as following.

Now let us understand two of the important concept which we want to learn in this blog post.

CHECKPOINT

Checkpoint writes all the data pages that are in the buffer cache and modified (but yet not written to disk) to disk. In other words, this operation flushes all the modified dirty pages to the disk.

Let us see the impact of this particular keywords on the SQL Server memory.

CHECKPOINT
GO

This particular command is specific to the database so you will have to specify your database name in the beginning of the command for which you want to run this operation.

DBCC DROPCLEANBUFFERS

Drop clean buffer operation removes all the buffers from the cache which contain the data already moved to the disk. In other words, this operation flushes out all the clean pages (which were dirty before CHECKPOINT executed) out of the memory.

DBCC DROPCLEANBUFFERS
GO

This particular command is specific to your server, and after running it, it will remove the clean pages from the buffer for all the databases.

NOTE: CHECKPOINT and DBCC DROPCLEANBUFFERS both should not be run on the production server without any specific reasons. While CHECKPOINT may not be as dangerous, DBCC DROPCLEANBUFFERS certainly may impact to lower your SQL Server performance temporarily till your cache is rebuilt.

I hope it is clear from the example of how the impact of both of the command on the Memory.

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