asked
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][1] 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][2] 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 [1]: /storage/temp/2273-resource-semaphore.png [2]: /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.
Thanks@Grant, i am focusing on query tuning as of now. Please confirm on two points below: 1. Found a Aggressive index as per sp_blitzindex, should i go ahead and delete that index? 2. MAX server setting was default value of 21478... and i changed it to 13500. I already have AWE enable as well as PAE and locked pages in memory also granted for service account. So do i have to reboot after that change in max server memory setting? Thanks
1. I have no idea what an "Aggressive" index is. 2. No, you shouldn't have to reboot.
15 People are following this question.
