SQL Server - recursive CTE to retrieve dependency chain of Resource Governor Classifier Function

SQL Server Resource Governor classifier functions can include references to functions as well as tables in the master database.  And each referenced function can in turn reference additional functions and tables.

In order to get a thorough understanding of how sessions are classified on a given system, one may need some familiarity with each function or table in the dependency tree.

This recursive function will retrieve all object names in the dependency tree (object names will be retrieved but column names will not be retrieved, due to the referenced_minor_id = 0 qualifier.

;WITH DTree AS 
(	SELECT		root_id		= classifier_function_id
	,		referencing_id	= classifier_function_id
	,		referenced_id	= classifier_function_id
	,		NestLevel	= 1
	FROM		sys.dm_resource_governor_configuration
	UNION ALL
	SELECT		root_id         = rgc.classifier_function_id
	,		d1.referencing_id
	,		d1.referenced_id
	,		NestLevel + 1
	FROM		sys.sql_expression_dependencies d1 
	JOIN		DTree r		ON d1.referencing_id =  r.referenced_id	
	CROSS JOIN	sys.dm_resource_governor_configuration rgc
	WHERE		d1.referenced_minor_id = 0)
SELECT		classifier_function	= OBJECT_NAME(t1.root_id)
,		referencing_name	= OBJECT_NAME(t1.referencing_id)
,		referenced_name		= OBJECT_NAME(t1.referenced_id)
,		referenced_type		= so.type_desc
,		t1.NestLevel
FROM		DTree t1
JOIN		sys.objects so		ON so.object_id = t1.referenced_id
ORDER BY	t1.NestLevel, t1.referencing_id;

SQL Server - recursive CTE to retrieve rowstore index IAM chain

 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);