Обнаружение длинных цепочек IAM

Автор: Paul Randal, The Curious Case of… finding long IAM chains

В предыдущей статье Любопытный случай периодического сбоя запроса к крошечной таблице я описал проблему, с которой Джонатан столкнулся у клиента: очень длинные цепочки 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