Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  SQL Server Administration (2008)  Resource_Semaphore WAITs

Author  Topic  TJTodd Starting Member 10 Posts Posted - 2011-02-24 : 12:56:48 We had a situation where there were many processes getting very long WAITs of type "RESOURCE_SEMAPHORE". I tried querying the DMV sys.dm_exec_query_memory_grants and expected to find the session(s) responsible for the problem. Usually when we have a problem with widespread waits of this type, I could query this DMV and see: a) at least one process that has been granted a large amount of memory (in the Granted_Memory_KB column), and b) many processes with a NULL value of Granted_Memory_KB. I actually join to some other DMVs and see that these are all WAITing on Resource_Semaphore Question is - why would I see all these processes showing up as Waiting for Resource_Semaphore but I don't see any process in sys.dm_exec_query_memory_grants that shows it's consuming memory? If there's not a large memory grant that has occurred, then why is everyone waiting? Thanks in advance, Tom