In the previous Curious Case I described an issue Jonathan had at a client with very long IAM chains, and the circumstances leading to it.
The question was how to prove that some allocation units had IAM chain lengths way out of proportion to the amount of data in the allocation unit, without tediously walking through each IAM chain, starting with the first IAM page (whose ID is always stored in sys.allocation_units internal table).
The answer was to do exactly that, but remove the tedium by writing some nifty code to do it, making use of the sys.dm_db_page_info DMF that was added in SQL Server 2019 instead of having to use DBCC PAGE with the results INSERT … EXEC‘d into a table.
(DMF? Yes, Dynamic Management Function. Remember – they’re all DMOs – Dynamic Management Objects – and either views or functions – DMVs or DMFs. DMVs just look up information but DMFs have to do some work. They’re just collectively called DMVs for simplicity.)
Specifically, the answer was for Jonathan to write the nifty code :-) and here it is. Give it a whirl and let me know if you find any indexes with massive IAM chains compared to the number of data or index pages.
;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] = OBJECTD_ID ('Schema.TableName')
OPTION (MAXRECURSION 0);
GO