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.


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.

  • 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
    Commented Mar 21, 2013 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
    Commented Mar 21, 2013 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
    Commented Mar 21, 2013 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
    Commented Mar 21, 2013 at 20:11
  • 4
    Maybe the vendor uses this troubleshooting guide... Commented Mar 21, 2013 at 21:07

1 Answer 1


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.

  • 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
    Commented Mar 22, 2013 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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