sys.dm_exec_query_optimizer_memory_gateways (Transact-SQL)
Applies to: 
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
Returns the current status of resource semaphores used to throttle concurrent query optimization.
| Column | Type | Description |
|---|---|---|
| pool_id | int | Resource pool ID under Resource Governor |
| name | sysname | Compile gate name (Small Gateway, Medium Gateway, Big Gateway) |
| max_count | int | The maximum configured count of concurrent compiles |
| active_count | int | The currently active count of compiles in this gate |
| waiter_count | int | The number of waiters in this gate |
| threshold_factor | bigint | Threshold factor which defines the maximum memory portion used by query optimization. For the small gateway, threshold_factor indicates the maximum optimizer memory usage in bytes for one query before it is required to gain an access in the small gateway. For the medium and big gateway, threshold_factor shows the portion of total server memory available for this gate. It is used as a divisor when calculating the memory usage threshold for the gate. |
| threshold | bigint | Next threshold memory in bytes. The query is required to gain an access to this gateway if its memory consumption reaches this threshold. "-1" if the query is not required to gain an access to this gateway. |
| is_active | bit | Whether the query is required to pass the current gate or not. |
Permissions
SQL Server requires VIEW SERVER STATE permission on the server.
Azure SQL Database requires the VIEW DATABASE STATE permission in the database.
Permissions for SQL Server 2022 and later
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
Remarks
SQL Server uses a tiered gateway approach to throttle the number of permitted concurrent compilations. Three gateways are used, including small, medium and big. Gateways help prevent the exhausting of overall memory resources by larger compilation memory-requiring consumers.
Waits on a gateway result in delayed compilation. In addition to delays in compilation, throttled requests will have an associated RESOURCE_SEMAPHORE_QUERY_COMPILE wait type accumulation. The RESOURCE_SEMAPHORE_QUERY_COMPILE wait type may indicate that queries are using a large amount of memory for compilation and that memory has been exhausted, or alternatively there is sufficient memory available overall, however available units in a specific gateway have been exhausted. The output of sys.dm_exec_query_optimizer_memory_gateways can be used to troubleshoot scenarios where there was insufficient memory to compile a query execution plan.
Examples
A. Viewing statistics on resource semaphores
What are the current optimizer memory gateway statistics for this instance of SQL Server?
SELECT [pool_id], [name], [max_count], [active_count],
[waiter_count], [threshold_factor], [threshold],
[is_active]
FROM sys.dm_exec_query_optimizer_memory_gateways;
See Also
Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)
How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005
Large query compilation waits on RESOURCE_SEMAPHORE_QUERY_COMPILE in SQL Server 2014