December 31, 2013 at 11:32 am
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.
January 1, 2014 at 12:39 am
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/
January 3, 2014 at 10:29 am
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).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply