SQL SERVER – Clean Pages and Dirty Pages Count – Memory Buffer Pools

While I work with my customers on SQL Server Performance Tuning Practical Workshop, I always try to categorize them into one of the following three issues 1) CPU, 2) Memory or 3) IO. Recently, while working with the customer who had a Memory issue, we ended up talking about Clean Pages and Dirty Pages Count.

Here is a quick script which will list details of your current database and how much memory each object is taking in the buffer pool.

SELECT
	SCHEMA_NAME(objects.schema_id) AS SchemaName,
	objects.name AS ObjectName,
	objects.type_desc AS ObjectType,
	COUNT(*) AS [Total Pages In Buffer],
	COUNT(*) * 8 / 1024 AS [Buffer Size in MB],
    SUM(CASE dm_os_buffer_descriptors.is_modified 
				WHEN 1 THEN 1 ELSE 0
		END) AS [Dirty Pages],
    SUM(CASE dm_os_buffer_descriptors.is_modified 
				WHEN 1 THEN 0 ELSE 1
		END) AS [Clean Pages],
    SUM(CASE dm_os_buffer_descriptors.is_modified 
				WHEN 1 THEN 1 ELSE 0
		END) * 8 / 1024 AS [Dirty Page (MB)],
    SUM(CASE dm_os_buffer_descriptors.is_modified 
				WHEN 1 THEN 0 ELSE 1
		END) * 8 / 1024 AS [Clean Page (MB)]
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.allocation_units ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id
INNER JOIN sys.partitions ON 
	((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))
	OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))
INNER JOIN sys.objects ON partitions.object_id = objects.object_id
WHERE allocation_units.type IN (1,2,3)
	AND objects.is_ms_shipped = 0 
	AND dm_os_buffer_descriptors.database_id = DB_ID()
GROUP BY objects.schema_id, objects.name, objects.type_desc
ORDER BY [Total Pages In Buffer] DESC;

If you are facing memory pressure for your SQL Server, you will find the above script very handy as it will display which particular object from your database is taking the maximum memory inside your buffer pool and how it is balancing with other objects in the table. Additionally, it is important to understand the difference between Clean Buffer and Dirty Buffer. Please read them to get a clear understanding between them.

Here are two blog posts, I strongly recommend if you want to understand the difference between clean pages and dirty pages.

Dirty Pages – How to List Dirty Pages From Memory in SQL Server?
What is Clean Buffer in DBCC DROPCLEANBUFFERS?

After looking at the data from the above query, at the client’s place, we were able to determine the offending table in our query and we backtracked the query which was creating lots of issues for the application. Once we fixed the query, the application started to work much faster. I may cover this in one of the future blog posts.

Here is my question to you – if you have to define your business SQL Server into one category which category you would categorize – 1) Memory Bound 2) CPU Bound or 3) IO Bound? Please leave a comment right below the blog.

If you have any question, do let me know and I will be happy to answer.

Reference: Pinal Dave (https://blog.sqlauthority.com)