RESOURCE_SEMAPHORE_QUERY_COMPILE
Published 28 March 2024
This occurs when a query is waiting for memory to be granted that will allow compilation to take place. Compile memory is taken from the buffer pool and kept for as long as is necessary to complete the compilation process. There is a risk that taking too many memory pages for multiple concurrent compilations could cause memory pressure. To mitigate this, SQL Server starts the compilation process, works out which queries will require a high number of pages, and forces some to wait. Similarly, if memory pressure already exists, SQL Server will limit the number of resource-intensive queries that can be compiled simultaneously.
If you’re seeing this wait on a frequent basis, you may have too many memory-intensive queries, or other resources may be stealing memory pages from the buffer pool.
Investigating excessive query compiles
- Check the following metrics on the Analysis page:
- Compilations/sec
- Compilations/batch
- Recompilations/sec
- Batch requests/sec
See each metric’s Description tab for guideline values and possible solutions.
- Check the affected queries and the Top queries table. To tune problematic queries, see: Query tuning (TechNet).
- Query the cache plans to check whether there are too many compiles and recompiles occurring, and reduce them if necessary. See: Identifying high compile time statements from the plan cache.
Investigating memory pressure
- Check for other memory waits, such as RESOURCE_SEMAPHORE and CMEMTHREAD.
- Check for memory issues by querying the DMV sys.dm_exec_query_memory_grants. See: sys.dm_exec_query_memory_grants (TechNet).
- Query the DMV sys.dm_exec_query_resource_semaphores to show the amount of memory held. See: sys.dm_exec_query_resource_semaphores (TechNet).
If you’re experiencing ongoing memory issues, consider installing memory-related custom metrics, such as Cached pages in TempDB and Buffer pressure.