Question: How to List Dirty Pages From Memory in SQL Server?
Answer: I recently received this question today during my SQL Server Performance Tuning Practical Workshop. To my surprise, I have already blogged about this in detailed earlier so during the workshop, I was able to forward the attendee to that particular question. Here is my previous blog post where I explained this concept in detail: SQL SERVER – How to View the Dirty Pages In Memory of a Database?
Now let us reproduce the script which I have used in the previous blog post over here, which will list all the dirty pages from the memory. Dirty pages are the pages which contain the data which are yet not committed to the hard drive.
SELECT database_name = d.name, OBJECT_NAME = CASE au.TYPE WHEN 1 THEN o1.name WHEN 2 THEN o2.name WHEN 3 THEN o1.name END, OBJECT_ID = CASE au.TYPE WHEN 1 THEN p1.OBJECT_ID WHEN 2 THEN p2.OBJECT_ID WHEN 3 THEN p1.OBJECT_ID END, index_id = CASE au.TYPE WHEN 1 THEN p1.index_id WHEN 2 THEN p2.index_id WHEN 3 THEN p1.index_id END, bd.FILE_ID, bd.page_id, bd.page_type, bd.page_level FROM sys.dm_os_buffer_descriptors bd INNER JOIN sys.databases d ON bd.database_id = d.database_id INNER JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id LEFT JOIN sys.partitions p1 ON au.container_id = p1.hobt_id LEFT JOIN sys.partitions p2 ON au.container_id = p2.partition_id LEFT JOIN sys.objects o1 ON p1.OBJECT_ID = o1.OBJECT_ID LEFT JOIN sys.objects o2 ON p2.OBJECT_ID = o2.OBJECT_ID WHERE is_modified = 1
(Please note that we are not talking about Dirty Reads here. We are talking about the dirty pages in memory which essentially means data which is in memory but not yet moved to hard drive).
You can execute CHECKPOINT and the data which is in the memory will be committed to a hard drive and the script will not return any results. If you are using an earlier version of SQL Server you may have to enable a trace flag 35015 as mentioned in this blog post.
Here are the few additional blog posts which are related to this topic:
In one of the recent projects, I was asked to create a report of queries that are waiting for memory allocation. The reason was that we were doubtful regarding whether the memory was sufficient for the application. The following query can be useful in a similar case. Queries that do not have to wait on a memory grant will not appear in the resultset of the query.
Reference: Pinal Dave (https://blog.sqlauthority.com)