How to resolve RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE wait types
Oh, goodness, I have some bad news here.

On a 32-bit OS, SQL Server only uses the first 4GB of memory for things like query workspace. (And it's fighting the OS for that 4GB, too - any other running apps will also compete for that memory.)

4GB might sound like a lot, but it's relatively easy to write a query that needs several GB of memory in order to run. When enough queries demand enough memory, SQL Server throws RESOURCE_SEMAPHORE waits because queries can't get enough memory in order to start. RESOURCE_SEMAPHORE_QUERY_COMPILE means they can't even get enough memory to compile an execution plan - and yeah, that's pretty bad.

So how do you fix it?


Switch to a 64-bit OS (the OS you're running is long out of support anyway)
Switch to a 64-bit build of SQL Server
Reduce memory demands on the server (don't run any other apps on this box, and that's especially critical on 32-bit boxes since we're capped at just 4GB)
Use more memory with the AWE/PAE switches - except that doesn't work for RESOURCE_SEMAPHORE waits because SQL Server can only use that first 4GB for query workspace
Tune queries &amp; indexes so they need less memory


I hesitate to even say that last one, because the 32-bit issue is so bad, and it's really hard on the older versions of SQL Server. If you were on a current one, you could go through the plan cache and sort queries by memory grant, find the biggest grant recipients, and tune those. Not an option on this old antique, though.