В предыдущей статье Любопытный случай периодического сбоя запроса к крошечной таблице я описал проблему, с которой Джонатан столкнулся у клиента: очень длинные цепочки IAM и обстоятельства, к ним приведшие.
Вопрос заключался в том, как доказать, что некоторые единицы распределения имеют длину цепочки IAM, непропорциональную объёму данных в единице распределения, без утомительного прохода по каждой цепочке IAM, начиная с первой IAM-страницы (чей идентификатор всегда хранится во внутренней таблице sys.allocation_units).
Ответ заключался в том, чтобы сделать именно это, но устранить утомительность, написав изящный код для этого, используя DMF sys.dm_db_page_info, добавленную в SQL Server 2019, вместо необходимости использовать DBCC PAGE с результатами, INSERT … EXEC'нутыми в таблицу.
(DMF? Да, динамическая административная функция. Помните – все они DMO – динамические административные объекты – и либо представления, либо функции – DMV или DMF. DMV просто просматривают информацию, а DMF должны выполнить некоторую работу. Их для простоты собирательно называют DMV).
В частности, ответ заключался в том, чтобы Джонатан написал этот изящный код :-) и вот он. Попробуйте и дайте мне знать, если найдёте какие-либо индексы с массивными цепочками IAM по сравнению с количеством страниц данных или индексов.
;WITH IAM_PAGES AS
(
SELECT
1 AS [IAM_Page_Ordinal],
P.[object_id],
P.[index_id],
P.[partition_number],
IAU.[total_pages],
IAU.[used_pages],
IAU.[data_pages],
IAM_Page.[file_id],
IAM_Page.[page_id],
[pfs_page_id],
[gam_page_id],
[sgam_page_id],
[next_page_file_id],
[next_page_page_id],
[is_iam_page]
FROM sys.partitions P
INNER JOIN sys.system_internals_allocation_units AS IAU
ON P.[hobt_id] = IAU.[container_id]
OUTER APPLY sys.fn_PageResCracker (IAU.[first_iam_page]) AS IAM_Page
OUTER APPLY sys.dm_db_page_info (
DB_ID (), IAM_Page.[file_id], IAM_Page.[page_id], 'DETAILED') AS Page_Info
WHERE IAM_Page.[page_id] <> 0 AND OBJECT_SCHEMA_NAME (P.[object_id]) <> N'sys'
UNION ALL
SELECT
[IAM_Page_Ordinal] + 1,
IAMP.[object_id],
IAMP.[index_id],
IAMP.[partition_number],
IAMP.[total_pages],
IAMP.[used_pages],
IAMP.[data_pages],
Page_Info.[file_id],
Page_Info.[page_id],
Page_Info.[pfs_page_id],
Page_Info.[gam_page_id],
Page_Info.[sgam_page_id],
Page_Info.[next_page_file_id],
Page_Info.[next_page_page_id],
Page_Info.[is_iam_page]
FROM IAM_PAGES AS IAMP
OUTER APPLY sys.dm_db_page_info (
DB_ID (), IAMP.[next_page_file_id], IAMP.[next_page_page_id], 'DETAILED') AS Page_Info
WHERE IAMP.[next_page_page_id] <> 0
),
IAM_Counts AS
(
SELECT
[object_id],
[index_id],
[partition_number],
[total_pages],
[used_pages],
[data_pages],
COUNT (*) AS [IAM_Page_Count]
FROM IAM_PAGES
GROUP BY [object_id], [index_id], [partition_number],
[total_pages], [used_pages], [data_pages]
)
SELECT * FROM IAM_Counts
WHERE [data_pages] < [iam_page_count]
-- AND [object_id] = OBJECT_ID ('Schema.TableName')
OPTION (MAXRECURSION 0);
GO
