{"id":5352,"date":"2026-02-24T18:36:27","date_gmt":"2026-02-25T02:36:27","guid":{"rendered":"https:\/\/www.sqlskills.com\/blogs\/paul\/?p=5352"},"modified":"2026-03-01T14:12:31","modified_gmt":"2026-03-01T22:12:31","slug":"the-curious-case-of-finding-long-iam-chains","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/","title":{"rendered":"The Curious Case of… finding long IAM chains"},"content":{"rendered":"

In the previous Curious Case<\/a> I described an issue Jonathan had at a client with very long IAM chains, and the circumstances leading to it.<\/p>\n

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<\/em> internal table).<\/p>\n

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<\/em><\/a> DMF that was added in SQL Server 2019 instead of having to use DBCC PAGE<\/em> with the results INSERT … EXEC<\/em>‘d into a table.<\/p>\n

(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.)<\/p>\n

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.<\/p>\n

\r\n;WITH IAM_PAGES AS\r\n(\r\n    SELECT\r\n        1 AS [IAM_Page_Ordinal],\r\n        P.[object_id],\r\n        P.[index_id],\r\n        P.[partition_number],\r\n        IAU.[total_pages],\r\n        IAU.[used_pages],\r\n        IAU.[data_pages],\r\n        IAM_Page.[file_id],\r\n        IAM_Page.[page_id],\r\n        [pfs_page_id],\r\n        [gam_page_id],\r\n        [sgam_page_id],\r\n        [next_page_file_id],\r\n        [next_page_page_id],\r\n        [is_iam_page]\r\n    FROM sys.partitions P\r\n    INNER JOIN sys.system_internals_allocation_units AS IAU\r\n        ON P.[hobt_id] = IAU.[container_id]\r\n    OUTER APPLY sys.fn_PageResCracker (IAU.[first_iam_page]) AS IAM_Page\r\n    OUTER APPLY sys.dm_db_page_info (\r\n            DB_ID (), IAM_Page.[file_id], IAM_Page.[page_id], 'DETAILED') AS Page_Info\r\n        WHERE IAM_Page.[page_id] <> 0 AND OBJECT_SCHEMA_NAME (P.[object_id]) <> N'sys'\r\nUNION ALL\r\n    SELECT           \r\n        [IAM_Page_Ordinal] + 1,\r\n        IAMP.[object_id],\r\n        IAMP.[index_id],\r\n        IAMP.[partition_number],\r\n        IAMP.[total_pages],\r\n        IAMP.[used_pages],\r\n        IAMP.[data_pages],\r\n        Page_Info.[file_id],\r\n        Page_Info.[page_id],\r\n        Page_Info.[pfs_page_id],\r\n        Page_Info.[gam_page_id],\r\n        Page_Info.[sgam_page_id],\r\n        Page_Info.[next_page_file_id],\r\n        Page_Info.[next_page_page_id],\r\n        Page_Info.[is_iam_page]\r\n    FROM IAM_PAGES AS IAMP\r\n    OUTER APPLY sys.dm_db_page_info (\r\n            DB_ID (), IAMP.[next_page_file_id], IAMP.[next_page_page_id], 'DETAILED') AS Page_Info\r\n        WHERE IAMP.[next_page_page_id] <> 0\r\n),\r\nIAM_Counts AS\r\n(\r\n    SELECT\r\n        [object_id],\r\n        [index_id],\r\n        [partition_number],\r\n        [total_pages],\r\n        [used_pages],\r\n        [data_pages],\r\n        COUNT (*) AS [IAM_Page_Count]\r\n    FROM IAM_PAGES\r\n    GROUP BY [object_id], [index_id], [partition_number],\r\n        [total_pages], [used_pages], [data_pages]\r\n)\r\nSELECT * FROM IAM_Counts\r\nWHERE [data_pages] < [iam_page_count]\r\n--  AND [object_id] = OBJECTD_ID ('Schema.TableName')\r\nOPTION (MAXRECURSION 0);\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,115],"tags":[],"class_list":["post-5352","post","type-post","status-publish","format-standard","hentry","category-inside-the-storage-engine","category-the-curious-case-of"],"yoast_head":"\nThe Curious Case of... finding long IAM chains - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Curious Case of... finding long IAM chains - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2026-02-25T02:36:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-03-01T22:12:31+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/\",\"name\":\"The Curious Case of... finding long IAM chains - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2026-02-25T02:36:27+00:00\",\"dateModified\":\"2026-03-01T22:12:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Curious Case of… finding long IAM chains\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Curious Case of... finding long IAM chains - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/","og_locale":"en_US","og_type":"article","og_title":"The Curious Case of... finding long IAM chains - Paul S. Randal","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/","og_site_name":"Paul S. Randal","article_published_time":"2026-02-25T02:36:27+00:00","article_modified_time":"2026-03-01T22:12:31+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/","name":"The Curious Case of... finding long IAM chains - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2026-02-25T02:36:27+00:00","dateModified":"2026-03-01T22:12:31+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-finding-long-iam-chains\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"The Curious Case of… finding long IAM chains"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5352","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=5352"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5352\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=5352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=5352"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=5352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}