Что находится в буферном пуле?

Автор: Inside the Storage Engine: What’s in the buffer pool?

Это короткая статья, навеянная вопросом из письма (спасибо, Маркос!), и заодно отличный повод показать DMV, о котором я давно хотел написать. А ещё сейчас в Редмонде ужасная погода, так что на улицу не выйти — блогинг послужит стратегией уклонения от работы этим днём :-).

(В пересказе) вопрос таков: Контрольная точка — это процесс, который записывает все «грязные» страницы на диск и работает в разрезе базы данных. Но если кеш данных может хранить страницу из любой базы, как же контрольная точка понимает, какие страницы проверять на «грязность»? Она просматривает буферный пул в поисках страниц конкретной базы X и обрабатывает только их? Или кеш данных как‑то разделён по базам? Хотелось бы лучше понимать, как это устроено «под капотом».

Ответ такой: страницы хранятся в буферах буферного пула (он же буферный кеш или кеш данных), и буферы действительно хэшируются, чтобы их было легко находить по базе данных. Посмотреть, какие страницы сейчас находятся в буферном пуле, и их состояние можно с помощью DMV sys.dm_os_buffer_descriptors:

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
.

Я обрезал вывод, вместо того чтобы перечислять все 3258 страниц в буферном пуле моего ноутбука. DMV возвращает часть сведений прямо из самих страниц (помните: всё это в памяти, так что находится быстро).

Поигравшись с DMV, я набросал удобный скрипт, который показывает, сколько «чистых» и «грязных» страниц находится в буферном пуле по каждой базе данных.

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

Найти скрипт, который показывает, какая доля конкретной таблицы находится в памяти, можно здесь. Приятного пользования!