Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Clean and Dirty Pages
Published: Dec 05, 2020
In an extension to my previous post about using CHECKPOINT in conjunction with DROPCLEANBUFFERS I thought I would give an example to highlight the situation.

This uses code from a previous post entitled “What’s Using The Buffer Pool?” which actually shows you the number of dirty and clean pages in the buffer pool but seemingly without explaining what that means… and since the last post touched upon this as well I figured I would knock up a quick and simple demo for you to run yourself.

Okay… let’s get started by creating ourselves a table to play with…

if object_id('salesData') is not null drop table salesData
go

select *
into salesData
from sales.SalesOrderHeaderEnlarged
go

checkpoint
dbcc
dropcleanbuffers
go


Now we’ll check the buffer pool…

if object_id('tempDB..#pagesInMemory') is not null drop table #pagesInMemory

select t.name tableName, i.name indexName, count_big(*) noPages
into #pagesInMemory
from sys.dm_os_buffer_descriptors b
inner hash join sys.allocation_units a
on b.allocation_unit_id = a.allocation_unit_id
inner hash join sys.partitions p
on a.container_id = p.partition_id
inner hash join sys.indexes i
on p.index_id = i.index_id
and i.object_id = p.object_id
inner hash join sys.tables t
on i.object_id = t.object_id
where database_id = db_id()
group by t.name, i.name

select *, convert(decimal(20, 2), convert(bigint, noPages) * 8192 / 1024 / 1024.) usedMB
from #pagesInMemory
order by usedMB desc



So now what we’ll do is pull the whole table into cache and then we’ll check the pages in cache based on Dirty and Clean split…

select *
from salesData
go

with bufferPool as
(
      
select case when is_modified = 1 then 'Dirty' else 'Clean' end pageType,
                    
case database_id when 32767 then 'Resource' else db_name(database_id) end databaseName,
                    
count_big(*) noPages
      
from sys.dm_os_buffer_descriptors
      
where database_id = db_id('AdventureWorks2012')
      
group by database_id, is_modified
)
select *, (noPages * 8) / 1024 / 1024. usedGB,
              
case when noPages = 0 or (noPages * 8) / 1024 / 1024. = 0 then 0
                          
else convert(decimal(5, 2), ((noPages * 8) / 1024 / 1024. / sum((noPages * 8) / 1024 / 1024.) over()) * 100)
              
end percentBufferPool
from bufferPool
order by usedGB desc



ALL the pages are marked as clean… this is because we have simply read the pages into memory… therefore they are effectively untouched.

So what we’ll now do is update 1 single record… this will update a few pages (SQL Server does a lot in the background… I’ll not be explaining here… all we’re interested in is that a few pages were updated)…

update salesData
set orderDate = current_timestamp
where salesOrderID = 1288149;

with bufferPool as
(
      
select case when is_modified = 1 then 'Dirty' else 'Clean' end pageType,
                    
case database_id when 32767 then 'Resource' else db_name(database_id) end databaseName,
                    
count_big(*) noPages
      
from sys.dm_os_buffer_descriptors
      
where database_id = db_id('AdventureWorks2012')
      
group by database_id, is_modified
)
select *, (noPages * 8) / 1024 / 1024. usedGB,
              
case when noPages = 0 or (noPages * 8) / 1024 / 1024. = 0 then 0
                          
else convert(decimal(5, 2), ((noPages * 8) / 1024 / 1024. / sum((noPages * 8) / 1024 / 1024.) over()) * 100)
              
end percentBufferPool
from bufferPool
order by usedGB desc



The critical part of the above is that you can see that there are now clean and dirty pages showing in the buffer pool. This is because we now have untouched pages and changed pages.

What we’ll do now is drop CLEAN buffers:

dbcc dropcleanbuffers;

with bufferPool as
(
      
select case when is_modified = 1 then 'Dirty' else 'Clean' end pageType,
                    
case database_id when 32767 then 'Resource' else db_name(database_id) end databaseName,
                    
count_big(*) noPages
      
from sys.dm_os_buffer_descriptors
      
where database_id = db_id('AdventureWorks2012')
      
group by database_id, is_modified
)
select *, (noPages * 8) / 1024 / 1024. usedGB,
              
case when noPages = 0 or (noPages * 8) / 1024 / 1024. = 0 then 0
                          
else convert(decimal(5, 2), ((noPages * 8) / 1024 / 1024. / sum((noPages * 8) / 1024 / 1024.) over()) * 100)
              
end percentBufferPool
from bufferPool
order by usedGB desc



As you can see… we still have the Dirty pages in the Buffer Pool!!!

THIS is the critical problem when you’re working with performance testing… if you want to see “worst case” performance (ie. You want disk IO to be a factor) then you need to ensure that ALL data is coming from disk… but if you happen to have recently updated an entire table and then you run DROPCLEANBUFFERS you could well be running a query against data that’s actually in memory when you don’t expect it.

Hence I always run a CHECKPOINT first… this will flush all dirty pages to disk and then the DROPCLEANBUFFERS will clean your buffer pool entirely… hence…

checkpoint
dbcc
dropcleanbuffers
go

if object_id('tempDB..#pagesInMemory') is not null drop table #pagesInMemory

select t.name tableName, i.name indexName, count_big(*) noPages
into #pagesInMemory
from sys.dm_os_buffer_descriptors b
inner hash join sys.allocation_units a
on b.allocation_unit_id = a.allocation_unit_id
inner hash join sys.partitions p
on a.container_id = p.partition_id
inner hash join sys.indexes i
on p.index_id = i.index_id
and i.object_id = p.object_id
inner hash join sys.tables t
on i.object_id = t.object_id
where database_id = db_id()
group by t.name, i.name



Hope this makes sense.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.