This is a quick post inspired by a question I was sent in email (thanks Marcos!) which very neatly lets me show a DMV I've been meaning to blog about for a while. And the weather here in Redmond really sucks right now so I can't go outside - blogging will serve as my work-avoidance strategy this afternoon :-).
The (paraphrased) question is:
A checkpoint is a process that writes all dirty pages to disk, and is per-database. So, if the data cache can hold a page from any database, how does checkpoint know which pages to check for a dirty status? Does it scan through buffer pool looking for pages for database X and process only those? Or is data cache somehow partitioned by database? I'd like to know a bit better how it works under the covers.
The answer is that pages are stored in buffers in the buffer pool (aka buffer cache or data cache), and the buffers are indeed hashed so they can easily be found by database. You can see what pages are currently in the buffer pool, and their status using the sys.dm_os_buffer_descriptors DMV in 2005:
SELECT
* FROM sys.dm_os_buffer_descriptors;
GO
database_id file_id page_id page_level allocation_unit_id page_type row_count free_space_in_bytes is_modified
----------- -------- -------- ----------- -------------------- -------------- ----------- ------------------- -----------
1 1 9 0 6488064 BOOT_PAGE 1 7362 0
1 1 6 0 6488064 DIFF_MAP_PAGE 2 6 0
1 1 7 0 6488064 ML_MAP_PAGE 2 6 0
1 1 104 0 262144 DATA_PAGE 100 4196 0
1 1 105 0 851968 DATA_PAGE 65 5041 0
1 1 106 0 262144 DATA_PAGE 197 413 0
1 1 107 0 262144 DATA_PAGE 207 23 0
1 1 108 1 262144 INDEX_PAGE 7 7949 0
.
.
I cut off the output rather than list all 3258 pages in the buffer pool on my laptop. The DMV gives you back some info from the pages themselves as well as you can see(remember all this is in memory so it's quick to find).
I played around with the DMV a little bit and came up with a neat script that will tell you may many clean and dirty pages there are in the buffer pool per-database.
SELECT
(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified]
ORDER BY [database_id], [is_modified];
GO
Page State Database Name Page Count
---------- ------------------------- -----------
Clean master 302
Dirty master 1
Clean tempdb 88
Dirty tempdb 52
Clean model 56
Clean msdb 622
Dirty msdb 5
Clean adventureworks 110
Clean DemoRestoreOrRepair 64
Clean DBMaint2008 88
Clean DemoFatalCorruption1 64
Clean DemoFatalCorruption2 64
Clean broken 64
Clean DemoFatalCorruption3 64
Clean DemoCorruptMetadata 111
Clean DemoDataPurity 88
Clean SalesDB 123
Clean DemoNCIndex 88
Clean shrinktest 88
Clean DemoRestoreOrRepairCopy 64
Clean DemoSuspect 64
Clean FileHeaderTest 96
Clean MultiFileDB 96
Clean HA2008 88
Clean SalesDB_Snapshot 21
Clean BootPageTest 88
Clean Resource Database 599
Later this week I'll try to blog a script that can tell you how much of a particular table is in memory. Enjoy!