jump to navigation

Looking into dirty pages in SQL Server November 13, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
trackback

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.

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

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!!!

Comments»

1. datzme - March 9, 2014

Superb, I really like your posts – I get to many internals of SQL Server. Thankyou for this and keep writing.!!!!!!!!!1


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: