A SQL Server recursive CTE can reach a maximum level of recursion of 32768.
Strting with the first IAM page for a rowstore index, the entire IAM chain can be retrieved, as each IAM page indicates the next page in the chain.
So a recursive CTE could reliably retrieve IAM chains for the rowstore indexes in a filegroup until the filegroup exceeds 128 TB in aggregate file size. (Because each IAM page accounts for database file usage in a ~4GB range of a file, for a single rowstore index.)
This CTE will retrieve an IAM chain. AFAIK, it's performance will beat the brakes off any similar query relying on sys.dm_db_database_page_allocations.
DECLARE @object_id INT = OBJECT_ID('NOTES_FROM_BEYOND'); DECLARE @index_id SMALLINT = 1; DECLARE @type_desc VARCHAR(20) = 'IN_ROW_DATA';
/*IN_ROW_DATA, ROW_OVERFLOW_DATA, LOB_DATA*/
;WITH start_page AS ( SELECT fp.file_id , fp.page_id FROM sys.system_internals_allocation_units siau JOIN sys.partitions sp ON siau.container_id = sp.partition_id CROSS APPLY sys.fn_PhysLocCracker(siau.first_iam_page) fp WHERE sp.object_id = @object_id AND sp.index_id = @index_id AND siau.type_desc = @type_desc ) , rec_cte AS ( SELECT next_page_file_id = CONVERT(BIGINT, sp.file_id) , next_page_page_id = CONVERT(BIGINT, sp.page_id) , iam_page_ordinal = 1 FROM start_page sp
UNION ALL SELECT CONVERT(BIGINT, dpi.next_page_file_id) , CONVERT(BIGINT, dpi.next_page_page_id) , iam_page_ordinal = iam_page_ordinal + 1 FROM rec_cte CROSS APPLY sys.dm_db_page_info(DB_ID(), next_page_file_id, next_page_page_id, 'DETAILED') dpi WHERE dpi.next_page_file_id <> 0 ) SELECT rc.iam_page_ordinal, file_id = rc.next_page_file_id, page_id = rc.next_page_page_id FROM rec_cte rc WHERE rc.next_page_file_id IS NOT NULL ORDER BY rc.iam_page_ordinal OPTION (maxrecursion 32767);
No comments:
Post a Comment