How to resolve RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE wait types
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: ![RESOURCE] 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 ![DMV] 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 : /storage/temp/2273-resource-semaphore.png : /storage/temp/2275-infosema.jpg
Because you're on 32bit OS, you really have limited choices for memory. Make sure you have AWE enabled. But overall, you're hitting memory limitations. Those are the indications. Now, because you're also hitting them for the query compile, you might want to look at the size and complexity of your queries. Simplification could help. Also, general query tuning might help as well. If you're getting lots of scans or hash joins instead of seeks, you could be using a lot more memory for query processing.