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.
Check It Out!
(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
4 thoughts on “The Curious Case of… finding long IAM chains”
WOW! That’s some walking.
I enhanced it a bit. I realize it was probably what was needed for you, I wanted it to be something I could provide a list for everyon3e to see at a quick glance. I sliced out the system tables and added the object and index names. Some show up twice so I have to look at that.
Let me know if I screwed up, I hope this is right.
;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]
INNER JOIN sys.objects O
ON P.[object_id] = O.[object_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 O.[type] = ‘U’
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
QUOTENAME(OBJECT_SCHEMA_NAME(C.[object_id])) + ‘.’ + QUOTENAME(OBJECT_NAME(C.[object_id])) AS [Full_Object_Name],
C.[index_id],
ISNULL(I.[name], ‘— HEAP —‘) AS [Index_Name],
I.[type_desc] AS [Index_Type],
C.[partition_number],
C.[total_pages],
C.[used_pages],
C.[data_pages],
C.[IAM_Page_Count],
CAST(C.[data_pages] AS FLOAT) / NULLIF(C.[IAM_Page_Count], 0) AS [Pages_Per_IAM_Ratio]
FROM IAM_Counts C
LEFT JOIN sys.indexes I
ON C.[object_id] = I.[object_id]
AND C.[index_id] = I.[index_id]
WHERE C.[data_pages] < C.[iam_page_count]
ORDER BY C.[IAM_Page_Count] DESC
OPTION (MAXRECURSION 0);
GO
Found it. The once that show up twice are in different data files. (mdf and ndf). A long time ago the database ran out of drive space and due to the file type at OS level only could be extended to 4TB. So I had them add a new drive on a new drive letter and let it expand.
When it was migrated to a new machine, I put the data files on both drive letters onto a single drive that’s expandable to 128TB(?).
Even so, I’m impressed.
each where or != is missing