Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
I also see in perfmon that processes blocked keeps varying between 0,1,2 16 and 24 –  sassyboy Mar 28 '12 at 15:45
1  
What is session_id 97 doing??? A RESOURCE_SEMAPHORE wait is typically when there is a memory-intensive query that isn't able to get the requried memory for query execution or optimization. –  Thomas Stringer Mar 28 '12 at 15:47
add comment

1 Answer

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.

share|improve this answer
1  
Would it also be worth looking into whether the plan cache is bloated by single-use plans and considering optimize for ad hoc workloads? –  James Dec 26 '12 at 8:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.