Are resource_semaphore_query_compile waits causing 100% CPU on server?

Of late, the cpu usage on the db server is consistently at 100%. Even at 2 in the night when there is almost no traffic on the website and the load is negligible.

While trying to find the root cause, I see a lot of waiting tasks with wait type resource_semaphore_query_compile? All of these have started appearing lately in sys.dm_os_waiting_tasks

0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E9E28  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E8F28  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E94C8  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x008DC988  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x008DD798  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E9C48  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x594364D8  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E8C58  NULL    NULL    resourceWait

Why is this happening all of a sudden when nothing has changed? Also, the server 'self healed' itself yesterday, but the problem is back today? How to debug and fix this

Have you checked active tasks too, in addition to waiting ones? Check sys.dm_exec_requests for offenders with high cpu_time values. Look in sys.dm_exec_query_stats for queries with high total_worker_time.

An explanation for what is happening could be that a plan changed to result in a very CPU intensive execution plan with a large memory requirement (eg. hash joins, sorts). Parallel table scans and joins on non-aligned partitions come to mind... Few queries execute but they consume high CPU (the query exec and query stats views will reveal this). The queries require a large memory grant and cause other instances of the same query to block on the resource grant. A look in sys.dm_exec_query_resource_semaphores will reveal if this is true. Solution would be to address the plan stability, perhaps deploy plan guides.

StackExchange Database Administrators Q#15723, answer score: 4