Looking into dirty pages in SQL Server
I have just finished a seminar in SQL Server 2012 and some of the people attending it (from all walks of life) were asking me about dirty pages,what they are and if I can provide a demo.
I will try to explain what dirty pages are and how they are flushed to finally to the disk.
I will also provide a demo, where we can see the dirty pages of a database.
SQL Server makes changes to the memory. So all our transactions take place in the memory.
Pages are loaded to the memory (if they are not already there) and all the updates take place in memory.
Dirty pages are the pages that have changed in memory since they were last loaded from disk.
Those pages are written back to the disk through an SQL Server periodic scheduling functionality widely known as checkpoints.
Checkpoints make sure that committed transactions are flushed to the disk.
It also marks the transaction log so server knows where it has to recover from.
I am going to use the Northwind database to make an update and then. You can use any database you want and perform a simple update.
I connect to my local instance of SQL Server 2012 Enterprise edition through windows authentication.
[sourcecode language="sql"]
USE NORTHWIND
GO
BEGIN TRANSACTION
UPDATE Products set CategoryID = 1
WHERE SupplierID IN (14,19)
COMMIT TRANSACTION
GO
SELECT DB_NAME(database_ID) AS 'Database',
COUNT(page_id) AS 'Dirty Pages'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified = 1
AND DB_NAME(database_id)='NorthWind'
GROUP BY DB_NAME(database_id)
ORDER BY COUNT(page_id) DESC;
GO
CHECKPOINT
GO
SELECT DB_NAME(database_ID) AS 'Database',
COUNT(page_id) AS 'Dirty Pages'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified = 1
AND DB_NAME(database_id)='NorthWind'
GROUP BY DB_NAME(database_id)
ORDER BY COUNT(page_id) DESC;
GO
[/sourcecode]
In lines 1-7 i create a simple update query
In lines 11-17 I see the dirty pages for that database.
In line 21 i do a manual checkpoint and pages are flushed to the disk.
Then in lines 24-30 I run the same query again (lines 11-17) and this time I see no dirty pages for the database.
Hope it helps!!!