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.

Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits?

The last two weeks one of our applications has had downtime due to RESOURCE_SEMAPHORE_QUERY_COMPILER waits. We've "fixed" them by rebuilding indexes; it was just a shot in the dark.

The application vendor has "dug in" and "found the root cause" which they claim is index fragmentation and their recommendation was to implement the workaround we'd already put in place: rebuild indexes weekly (was in place at time of issue) and reorganize nightly (new).

My concern is the application vendor just took our work and claimed it as their own. When we originally stumbled across the fix, we considered it a temporary workaround unlikely to cause issues (its hard to object to rebuilding/reorganizing indexes). This issue started just after our largest user's busy time, so usage and index fragmentation was down. We had just purged a significant amount of data, so percentage fragmentation would get higher easier (it peaked about 22% when we were having the semaphore issue) but the percentages were still significantly down.

My concern is just we're putting a band-aide on the situation and that its just a matter of time before we have issues again. If the query was taking a long time, I could understand, but it seems odd to me that we'd be having memory issues while compiling the query.

Random bits of information:

  • Two application servers with about 130 concurrent users
  • Dedicated database server
    • Microsoft SQL Server 2008 Enterprise
    • 2 x Xeon E5-2650 @ 2.3 GHz
    • 64 GB of ram (don't know details)
    • Gigabit Ethernet x 3
  • The datafile for the database is 199GB
  • According to sp_spaceused
    • database_size is 37831736 KB
    • index_size is 54547920 KB
  • The log and datafile are one dedicated drives; both drives are on iSCSI SAN and are RAID10
  • There is no partitioning in the database

I apologize if I left off something important; let me know and I'll try to add it.

Edit:

When we reorganize daily, we are recompiling statistics and obviously when we rebuilt indexes that would be done, so I'll spend some time looking into our query plans. I really appreciate the guidance.

share|improve this question
    
I kind of think your weekly defragging procedure is no more than a band-aid, as you already feel. I'd say you're on some case of memory pressure. Please try Remus's hints in this previous question. –  Marian Mar 21 '13 at 20:07
    
I'm hoping this is not transactional if you have 54 GB of indexes for 37 GB of data. If it is you may be over-indexed. –  JNK Mar 21 '13 at 20:07
    
I think COMPILE waits like that could also come from high volumes of recompiles. Have you looked at query plan use/reuse? –  JNK Mar 21 '13 at 20:10
    
What other important stats can you pull when you're seeing these waits? What are the waiting tasks? What about query plan cache stats? OS Paging stats? CPU stats? Compilations/sec? –  swasheck Mar 21 '13 at 20:11
4  
Maybe the vendor uses this troubleshooting guide... –  Remus Rusanu Mar 21 '13 at 21:07
show 2 more comments

1 Answer

up vote 4 down vote accepted

Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits?

No. At least not directly, and even indirectly is not fragmentation but could be outdated stats at play (which rebuilding index also fixes). Read Plan Caching and Recompilation in SQL Server 2012, even though is 2012 a lot of it applies to 2008 still. The problem is that your plans are not reused and you suffer from high re-compilations. Read the white-paper, it has tonnes of resources and explains a lot.

share|improve this answer
    
I marked this as the answer; I haven't tried it yet but I had read about Plan Caching and Recompilation and had it on my to-do list. I wanted to validate that our "fix" wasn't really a fix. I really appreciate everyone's responses! –  W3t Tr3y Mar 22 '13 at 12:34
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.