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.
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)