How to resolve RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE wait types

Question

We are trying to figure out root cause of slow running sql server queries hitting/fetching data from one of the database , size 300 GB, hosted on server with below configuration:

Windows server 2003 R2, SP2, Enterprise Edition, 16 GB RAM , 12 CPU'S 32 Bit

SQL server 2005, SP4, Enterprise Edition, 32 Bit.

We have already informed business on the upgrade to 64 bit which would take over a month.

But for the current issue, we are trying to gather the data if we can resolve the memory pressure or finally come to a conclusion to increase RAM.

Action Completed: Re-indexing and update stats are proper for this DB.

As shown below, we have been noticing the semaphore waittype for past 5 days, ran during the load hours:

Few info after below queries: size of buffer= 137272

SELECT SUM(virtual_memory_committed_kb)
FROM sys.dm_os_memory_clerks
WHERE type='MEMORYCLERK_SQLBUFFERPOOL'

and semaphore memory= 644024 per below query

 SELECT SUM(total_memory_kb)
FROM sys.dm_exec_query_resource_semaphores

Below is some more info gathered from dm_exec_query_resource_semaphores and sys.dm_exec_query_memory_grants dmv's

So from above info gathered and per SP_Blitz data Resource semaphore seems to be the problem.

Is memory 'target_memory_kb' assigned for resource semaphore id's too low, as compared to 16 GB RAM available.

Note* per analysis on 8 hours run 'target_memory_kb' is always under 1 GB, compared to 16 GB available?

what could be the issue here and how to resolve, please suggest

Thanks

Answer 1

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 & 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.