How It Works: Non-Yielding Resource Monitor

I have addressed similar conditions error in a white paper: www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx

2008-01-20 19:01:26.11 Server ***Stack Dump being sent to K:MSSQL.3MSSQLLOGSQLDump0001.txt

2008-01-20 19:01:26.11 Server *

2008-01-20 19:01:26.11 Server * BEGIN STACK DUMP:

2008-01-20 19:01:26.11 Server * 01/20/08 19:01:26 spid 0

2008-01-20 19:01:26.11 Server *

2008-01-20 19:01:26.11 Server * Non-yielding Resource Monitor

SQL Server 2005 added the additional Resource Monitor progress check along with the 1788* series (17883, 17884, 17887, 17888) of checks.   Resource Monitor sets a start cache notification time when it begins processing actions against a specific cache.   When the cache processing is completed the time is cleared.

SchedulerMonitor executes (every 5 seconds) and checks to see if the Resource Monitor is processing a cache and for how long.   If the Resource Monitor has not returned to an idle state or advanced past a single cache in 60 seconds the message is logged and mini-dump is captured.

SQL Server 2005 can log this message falsely when cleaning up a large amount cache entries.   The most frequently seen false report by CSS is after a query with a huge lock list completes.   The lock lists are populated with many free lock resources that resource monitor can attempt to release.   If the work to release the entries takes longer than 60 seconds the message is produced but there is no real problem for the SQL Server.

Using the mini-dump and the Windows Debuggers with the public symbols, as outlined in the before mentioned white paper find the ResourceMonitor thread.   Here is an example of it maintaining a large lock free list.

...

sqlservr!commondelete+0x850

sqlservr!LockManager::Destroy+0x50

sqlservr!LockOwner::Destroy+0x20

sqlservr!SOS_ObjectStore::ObjectSubStore::DestroyObjects+0x260

sqlservr!SOS_ObjectStore::Notify+0xc0

ResourceMonitor::NotifyMemoryConsumers+0x580

ResourceMonitor::ResourceMonitorTask+0x220

...

Bug: There is a known bug that can cause the Resource Monitor to deadlock when processing entries associated with the security cache.   This has been corrected in SQL Server 2005 Service Pack 1.

In many instances this message is a warning of a negative influence from external factors.   The Resource Monitor runs on a hidden scheduler so it has limited impact on active connections.   If you can't determine the problem using the public symbols make sure you capture a full set of performance monitor counters during a problem occurrence and look closely at memory usage on the computer.

Bob Dorr
Senior SQL Server Escalation Engineer