SQL Server: Observing Dirty Pages in Memory

Hi Friends,

I wrote a blog post sometime back where I talked about observing the data cache, database-wise. I showed how you can monitor the amount of memory consumed by the data pages residing in the buffer pool, and in turn, find out the memory consumed by each database. You can find that blog post here;

When a page is brought in to memory by the buffer manager, it is a clean page. When that page is updated, either by a user query or by the system, it referred to as a dirty page. Simply because the version in the memory is different from its counterpart on the disk. And as many of you know, a CHECKPOINT process will write that page from memory to disk. So until the CHECKPOINT process is run, the page in the memory is still dirty. And after the CHECKPOINT process runs and does the needful, the page is marked as clean.

On a default system, the CHECKPOINT process runs every one minute. Yes, there are other events that can trigger a CHECKPOINT process, like taking a BACKUP. Or you can explicitly fire up the CHECKPOINT process by running the CHECKPOINT statement. So, I am done with a little bit of background info. Now, let us see how you can observer dirty pages in memory.

We can use the DMV sys.dm_os_buffer_descriptors to track dirty pages. This DMV returns a column is_modified which is flagged off as 1 in case the page is modified in the memory, else the value is 0. When you run the following query, you can observe the number of dirty pages in memory for each database: (Run this query after restarting the SQL instance on your test machine)

SELECT db_name(database_id) AS 'DatabaseName',count(page_id) AS 'DirtyPages', count(page_id)*8/1024 AS 'TotalSize(MB)'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified =1
GROUP BY db_name(database_id)
ORDER BY count(page_id) DESC

I will again use AdventureWorks as an example here. The above query probably will not show you AdventureWorks database. In case the output shows AdventureWorks database, chances are that you might have run an update query.
Now, let us run an update statement and immediately after the update statement, fire the above query again. Here is the code:

UPDATE Person.Contact
SELECT db_name(database_id) AS 'DatabaseName',count(page_id) AS 'DirtyPages', count(page_id)*8/1024 AS 'TotalSize(MB)'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified =1
GROUP BY db_name(database_id)
ORDER BY count(page_id) DESC

You will notice that AdventureWorks now shows up in the list with some 500+ pages that are dirty. You can now kindly ROLLBACK to undo the changes  – Do not COMMIT by mistake or else all contacts will have ‘Amit’ as first name 🙂

You can also play around by firing the CHECKPOINT statement manually and observe the behavior.
Now, you must be wondering, what is the practical use of observing dirty pages, etc. Well, when you want to monitor memory usage and consumption, there are a number of metrics you would like to record and this is an interesting metric to observe the memory internals; especially in an OLTP system where you want to monitor the update frequencies etc.



Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

2 Comments on “SQL Server: Observing Dirty Pages in Memory”

  1. Hi amit,

    What do you think will be the behavior if a checkpoint occurs in middle of an long running inflight transaction i.e the dirty pages,log records and the pages on the disk ? Will they all be in a mismatch ?

  2. I think the behavior will be that the dirty pages might be written to the disk – since in such a case the is_modifed flag is set to 0 – and this behavior also makes sense or else the DB engine might be under intense memory pressure if there are too many long transactions – hope this make sense ! – you can test this by running my above query !

Leave a Reply

Your email address will not be published. Required fields are marked *