How Dirty or Clean is SQL SERVER’s Memory? – Interview Question of the Week #229

Question: How Dirty or Clean SQL SERVER Database is (by Memory)?

Answer: This is a follow-up post of my previous blog here SQL SERVER – Clean Pages and Dirty Pages Count – Memory Buffer Pools. I strongly suggest you read that blog post before continuing this blog post to get the context of the topic.

After reading the blog post lots of people asked me it was great to know which object is taking the space in the buffer pool but on their server, however, many readers have numerous databases and they do not know which database to start their investigation.

After receiving many emails I have decided to write up a very small modification to the original script and build a script which will count all the clean and dirty pages for your database. I have also included the system database in the query as well. You can easily check on you TempDB with this script as well.

If you are not sure what clean pages or dirty pages is, here are two blog post you must read before you run the query on your server

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

Here is the script for the database which will list all the dirty pages and clean pages in your database along with their size.

SELECT
	DB_NAME(dm_os_buffer_descriptors.database_id) DatabaseName,
    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.databases ON dm_os_buffer_descriptors.database_id = databases.database_id
GROUP BY DB_NAME(dm_os_buffer_descriptors.database_id)
ORDER BY [Total Pages In Buffer] DESC;

If you run the above query, you will get similar results like the following image.

Well, Now you know from which database to start your journey. I would always pick the user database and will start my own investigation. Let me know if you need any help with turning your SQL Server Performance, I would like to help you improve the performance of your entire server while working together on Comprehensive Database Performance Health Check.

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