RESOURCE_SEMAPHORE

RESOURCE_SEMAPHORE waits occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.

High waits on RESOURCE_SEMAPHORE usually result in poor response times for all database users, and need to be addressed.

The "DBTuna Waits Report" displays a time-series profile of wait events for your monitored instance. The example below is taken from a SQL Server 2000 instance which suffered from sporadic problems which resulted in a spike in RESOURCE_SEMAPHORE waits.

It is also useful to correlate high waits on RESOURCE_SEMAPHORE with the Memory Grants Pending and Memory Grants Outstanding SQL Memory Mgr performance counters. Higher values for these counters indicate a definite memory problem especially a non-zero value for Memory Grants Pending.

The root cause of this type of memory problem is when memory-intensive queries, such as those involving sorting and hashing, are queued and are unable to obtain the requested memory. The solution would be to tune the offending queries, or manage their workload so that they are executed at less busy times.

If you would like to find out more about the performance of your SQL Server instances then why not download DBTuna today! Or arrange a web meeting at a convenient time via our contact page.

Copyright ©2010 DBTuna. All rights reserved.