Are resource_semaphore_query_compile waits causing 100% CPU on server?

Question

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

Answer 1

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.