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:

BEGIN TRAN
UPDATE Person.Contact
SET FIRSTNAME = 'AMIT'
GO
 
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.