Dirty Pages – How to List Dirty Pages From Memory in SQL Server? – Interview Question of the Week #223

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:

SQL SERVER – Queries Waiting for Memory Allocation to Execute

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)