SQL Inside the Storage Engine: What’s in the buffer pool?

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 thesys.dm_os_buffer_descriptors DMV in 2005:

?

1 2	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.

?

1 2 3 4 5 6 7 8

SELECT    (CASE WHEN ([is_modified] = 1) THEN N'Dirty' ELSE N'Clean' END) AS N'Page State',    (CASE WHEN ([database_id] = 32767) THEN N'Resource Database' ELSE DB_NAME ([database_id]) END) AS N'Database Name',    COUNT (*) AS N'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 ([Edit 12/8/11: that script is here]). Enjoy!