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.