Every day when you work with different clients, I often get the same question in different words. For example recently during Comprehensive Database Performance Health Check, I was asked what is the difference between Dirty Pages and Clean Pages in SQL Server Buffer Memory and which one is better? Let us quickly discuss that today.
Dirty Pages: Dirty pages are the pages in the memory buffer that have modified data, yet the data is not moved from memory to disk.
Clean Pages: Clean pages are the pages in a memory buffer that have modified data but the data is moved from memory to disk.
Well, that’s it. It is that simple of definition. Now if you are asking which one is better for performance, here is my response. It does not matter which one is there in the memory as users usually do not have control over what goes in the memory and what goes in the hard drive. Even though we can measure them and also change them using either CHECKPOINT or DBCC FREEPROCCACHE, one should not use them to make the modifications. It is quite possible that using the commands may negatively impact the overall health of your system.
Here is another interesting article which talks about the Impact of CHECKPOINT and DBCC DROPCLEANBUFFERS on Memory.
Here are a few additional blog posts related to this topic, and one should read them to understand how CHECKPOINTs works.
- SQL SERVER – Increasing Speed of CHECKPOINT and Best Practices
- Dirty Pages – How to List Dirty Pages From Memory in SQL Server?
- What is Clean Buffer in DBCC DROPCLEANBUFFERS?
Feel free to reach out to me on Twitter for any questions or comments.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
