Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Resource_semaphore wait type not matching sys.dm_exec_query_memory_grants Expand / Collapse
Author
Message
Posted Tuesday, December 31, 2013 11:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 03, 2014 3:06 PM
Points: 25, Visits: 131
We are using Red Gate's SQL Monitor to alert (every 5 minutes) when memory grants pending are greater than zero. Several times a day we are alerted on our Devel and Prod servers. However, when querying the resource_semaphore wait type using sys.dm_os_wait_stats, it used to register non-zero amounts several months ago, but now is always equal to zero.

The Devel server is version 11.0.3128 standard on Windows 2008 R2 Standard 64-bit, and the Prod server is version 10.0.5500 on Windows 2008 Standard 64-bit.

We are storing wait stats for each server in a database, collected every half hour. The Prod resource semaphore was 569 early August 11, then after a reboot at 3:30 AM was equal to zero and is the same today. I have confirmed the current value using the following query.

SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type = 'RESOURCE_SEMAPHORE';

Similarly, the Devel resource semaphore was 167 early October 23, then after a reboot at 7:03 AM was equal to zero and has not changed from zero since then. Again, I confirmed the current value using the above query.

The custom alert code for Red Gate SQL Monitor is:
SELECT COUNT(*) FROM sys.dm_exec_query_memory_grants;

Also, running a perfmon counter against SQL Server: Memory Manager: Memory Grants Pending for several days yields a flat line equal to zero, with maximum value zero.

Looks to me like something in SQL Server's internal stats collection system has broken. Or possibly, I am misunderstanding that these three counters measure the same thing.

Thanks for your help.
Post #1526821
Posted Wednesday, January 01, 2014 12:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:39 AM
Points: 2,104, Visits: 5,378
I think that the problem is with the query that you are using for the alert. The DMV sys.dm_exec_query_memory_grants has records for memory grants that are waiting and memory grants that were already granted and are still in use. If the memory request was not granted and the process is waiting for a memory grant then the column grant_time should have a null value. Try changing your alert to
select count(*) 
from sys.dm_exec_query_memory_grants
where grant_time is null or grant_time <> request_time

Adi




--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1526888
Posted Friday, January 03, 2014 10:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 03, 2014 3:06 PM
Points: 25, Visits: 131
Interesting. I was working off of the DMV documentation in Technet, which reads:

Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute. Queries that do not have to wait on a memory grant will not appear in this view.

If the above is correct, then grant_time should never equal request_time. In either case, it should not hurt to make the change you suggest.

That is, unless the precision of request_time and grant_time is not enough to note differences. Technet says that the data type for these columns is datetime for SQL 2008, SQL 2008 R2, and SQL 2012. Datetime has a precision of 1,000ths of a second (e.g. 12/10/2013 08:13:57.123).
Post #1527651
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse