SQL Server: Get memory gateway counts from DBCC MEMORYSTATUS

GetGatewayCounts.sql

/*
	Author: Tim Cartwright, Daniel Berber
	Purpose: To get the compile gateway counts out of sql server. Most likely if you are hitting gateway issues, 
		you are seeing RESOURCE_SEMAPHORE_QUERY_COMPILE wait stats. The gateways act as a sort of funnel. 
		The more compile memory a plan needs, the bigger the gateway required. If the number of queries being compiled
		for that gate way exceed the available count then the following queries will get stuck waiting with the 
		RESOURCE_SEMAPHORE_QUERY_COMPILE wait stat. 


	If you are using SQL Server 2016 or greater you can use: sys.dm_exec_query_optimizer_memory_gateways

	https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-optimizer-memory-gateways?view=sql-server-ver15
*/

IF OBJECT_ID('tempdb..#mem_status') IS NOT NULL BEGIN 
       DROP TABLE #mem_status 
END

create table #mem_status (
       id INT NOT NULL IDENTITY,
       counter varchar(80) null,
       value bigint NULL
)
insert into #mem_status (counter, value) exec ('dbcc memorystatus')

-- have to find the first row as the output row count is not always static
DECLARE @firstId INT = (
	SELECT ID FROM (
		SELECT row = ROW_NUMBER() OVER (ORDER BY id), ID from #mem_status  
		WHERE counter = 'Configured Units' 
	) t WHERE t.row = 4
)

SELECT * FROM (
	-- small gateway
	SELECT 'Small Gateway (default)' as dataset, 
		counter, 
		value
	FROM #mem_status 
	WHERE id >= @firstId AND id <= @firstId + 5
		UNION
	-- medium gateway
	SELECT 'Medium Gateway (default)' as dataset, 
		counter, 
		value
	FROM #mem_status 
	WHERE id >= (@firstId + 6) AND id <= (@firstId + 11)
		UNION
	-- large gateway
	SELECT 'Big Gateway (default)' as dataset, 
		counter, 
		value
	FROM #mem_status 
	WHERE id >= (@firstId + 12) AND id <= (@firstId + 17)
) gw 
--WHERE gw.counter = 'Waiters' AND gw.value > 0