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.
Best Answer
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.