I always get an opportunity to talk on topics that are basic from time to time. As I was preparing for GIDS 2015 (Great Indian Developer Summit) this year, I was vividly remembering one of the conversations I had with an attendee last year at our Pluralsight booth. Most of these conversations are forgotten, but this one was unique. During this conversation, the attendee was trying to learn the basics and was curious to listen to me. I always get carried away and spend quite some time with folks who are eager to learn. In this context, I was talking about how RDBMS’s use the concept of Write-Ahead Transaction Log for maintaining ACID properties. The response I got was interesting – “Hey Pinal, is there a way to know which of the pages in memory are currently dirty? Do you know how to get this?”
As I was preparing for this year’s session, this question was on top of my mind and I thought let me take a moment in this blog to show you how this can be achieved. Again, everything we do inside SQL Server can be accessed by the Dynamic Management views and it is important for us to know which ones to use.
Create the dummy database for test and we will enable few traceflags for this demo.
CREATE DATABASE DirtyPagesDB GO USE DirtyPagesDB GO -- Disable automatic checkpoint so that data isn't flushed -- DBCC TRACEON(3505, -1); DBCC TRACESTATUS(); GO
Let us create a table and enter some values. We will also flush the page using Checkpoint.
CREATE TABLE t1 (Speaker_Bio CHAR(8000)) GO INSERT INTO t1 VALUES ('SQL'),('Authority') GO CHECKPOINT
Let us check if there are any pages dirty currently in our buffer. Since we have done a checkpoint, there will be no rows for the below query.
-- Get the rows of dirtied pages SELECT database_name = d.name, OBJECT_NAME = CASE au.TYPE WHEN 1 THEN o1.name WHEN 2 THEN o2.name WHEN 3 THEN o1.name END, OBJECT_ID = CASE au.TYPE WHEN 1 THEN p1.OBJECT_ID WHEN 2 THEN p2.OBJECT_ID WHEN 3 THEN p1.OBJECT_ID END, index_id = CASE au.TYPE WHEN 1 THEN p1.index_id WHEN 2 THEN p2.index_id WHEN 3 THEN p1.index_id END, bd.FILE_ID, bd.page_id, bd.page_type, bd.page_level FROM sys.dm_os_buffer_descriptors bd INNER JOIN sys.databases d ON bd.database_id = d.database_id INNER JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id LEFT JOIN sys.partitions p1 ON au.container_id = p1.hobt_id LEFT JOIN sys.partitions p2 ON au.container_id = p2.partition_id LEFT JOIN sys.objects o1 ON p1.OBJECT_ID = o1.OBJECT_ID LEFT JOIN sys.objects o2 ON p2.OBJECT_ID = o2.OBJECT_ID WHERE is_modified = 1 AND d.name = 'DirtyPagesDB' AND ( o1.name = 't1' OR o2.name = 't1' ); GO
Since we got no rows, let us insert a row to check.
INSERT INTO t1 VALUES ('Pinal') GO
Go ahead and run the above big query again. You are likely to see 2 rows, one for IAM and one for Data page. If you run the CHECKPOINT, these will also get flushed to the data disk.
-- Clean up USE MASTER GO DROP DATABASE DirtyPagesDB GO
This was in my mind for a long time to research about this and write. I am sure this will be a good learning for my blog readers too. Do let me know if this was an interesting learning today for you.
Reference: Pinal Dave (https://blog.sqlauthority.com)