RESOURCE_SEMAPHORE_QUERY_COMPILE

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.

Investigating memory pressure

If you’re experiencing ongoing memory issues, consider installing memory-related custom metrics, such as Cached pages in TempDB and Buffer pressure.


Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com


Didn't find what you were looking for?