Select the product you need help with

FIX: Resource Monitor enters a non-yielding condition on a server that is running SQL Server 2008 or SQL Server 2008 R2

Article ID: 2216485 - View products that this article applies to.

SYMPTOMS

On a server that is running Microsoft SQL Server 2008 or SQL Server 2008 R2, the Resource Monitor task logs the following message every five seconds:

Date_And_Time Server Using 'dbghelp.dll' version '4.0.5'
Date_And_Time Server **Dump thread - spid = 0, PSS = 0x0000000000000
000, EC = 0x0000000000000000
Date_And_Time Logon Login succeeded for user 'OPENTEXT\sqlcrmusr'. Connection: trusted. [CLIENT: IP_Address]
Date_And_Time spid78 Error: 4014, Severity: 20, State: 2.
Date_And_Time spid78 A fatal error occurred while reading the input stream from the network. The session will be terminated.
Date_And_Time Server ***Stack Dump being sent to Drive:\MSSQL2005\LOG\SQLDump####.txt
Date_And_Time Server * *******************************************************************************
Date_And_Time Server *
Date_And_Time Server * BEGIN STACK DUMP:
Date_And_Time Server * Date_And_Time spid 0
Date_And_Time Server *
Date_And_Time Server * Non-yielding Resource Monitor
Date_And_Time Server *
Date_And_Time Server * *******************************************************************************
Date_And_Time Server * -------------------------------------------------------------------------------
Date_And_Time Server * Short Stack Dump
Date_And_Time Server Stack Signature for the dump is 0x000000000000005C

Date_And_Time,Server,Unknown,Resource Monitor (0x9b0) Worker 0x0000000003A2C1C0 appears to be non-yielding on Node_#. Memory freed: 0 KB. Approx CPU Used: kernel 0 msnull user 0 msnull Interval: Interval_value.

Back to the top | Give Feedback

CAUSE

In SQL Server 2008 and SQL Server 2008 R2, the Resource Monitor task wakes up periodically to listen to and notify subscribers of low, high, or steady memory events. These memory events may be external to SQL Server (notifications that are system-wide from the operating system) or internal to SQL Server (notifications that are process-wide from the buffer pool). When such notifications occur, various memory consumers trim their memory usage.

Note Consumers can be memory clerks that are cache stores, user stores, or object stores.

If certain memory consumers use a large amount of memory, the trimming that the consumers perform may take a long time to finish.

The Scheduler Monitor task that runs every five seconds checks whether Resource Monitor has moved from one consumer to another in the past 60 seconds. When the Scheduler Monitor detects that the Resource Monitor has not moved past a consumer for 60 seconds, the Schedule Monitor interprets this as the Resource Monitor entering a non-yielding state, and then the Schedule Monitor logs the error message that is mentioned in the "Symptoms" section.

These messages are also raised if the rate at which the Resource Monitor frees memory is less than 2 MB every five seconds.

These messages are only an indication that the Resource Monitor is busy cleaning up large consumers, and these messages do not necessarily indicate a problem with the Resource Monitor itself.

Back to the top | Give Feedback

RESOLUTION

Starting with Microsoft SQL Server 2008 Service Pack 2 and Microsoft SQL Server 2008 R2 Service Pack 1, the non-yielding Resource Monitor message was extended to easily isolate the memory clerk that leads to the non-yielding condition.

The new message will resemble the following:

Resource Monitor (0x9b0) Worker 0x0000000003A2C1C0 appears to be non-yielding on Node Node_#. Memory freed: 0 KB. Last wait: lastwaittype. Last clerk: type clerk_type, name clerk_name. Approx CPU Used: kernel 0 ms, user 0 ms, Interval: Interval_value.

The following are descriptions of the various fields that are used in this message:

Memory freed: This is how much memory is freed by Resource Monitor for the specified interval as measured in kilobytes. If the rate at which the memory is freed does not exceed 2 MB every five seconds, the Scheduler Monitor detects this condition as a non-yielding condition.
Last wait: This is the last wait type for the Resource Monitor thread. You can use this field together with the Approx CPU Used field to identify whether the Resource Monitor thread is running or waiting for a significant part of the interval.
Last clerk: This is the type and name of the memory clerk that was trimming its memory when the non-yielding condition occurred.
Approx CPU Used: This is the kernel and user time that is used by Resource Monitor as measured in milliseconds. You can use this together with other fields to verify that Resource Monitor is making progress during the specified interval.
Interval: This is the time that elapsed since the last clerk was notified as measured in milliseconds.

You can use this message in addition to the RING_BUFFER_RESOURCE_MONITOR entries from the time of the message to identify the source of the low memory notification.

For more information about how to interpret the RING_BUFFER_RESOURCE MONITOR. visit the following MSDN blog:

How It Works: What are the RING_BUFFER_RESOURCE_MONITOR telling me?

(http://blogs.msdn.com/b/psssql/archive/2009/09/17/how-it-works-what-are-the-ring-buffer-resource-monitor-telling-me.aspx)

For more information about how SQL Server listens and responds to memory notifications and about the Resource Monitor task and troubleshooting memory-related performance issues in SQL Server, refer to the following MSDN blog articles:

Memory Pressure - Classified

(http://blogs.msdn.com/b/slavao/archive/2005/02/01/364523.aspx)

SQLOS's memory manager: responding to memory pressure

(http://blogs.msdn.com/b/slavao/archive/2005/02/19/376714.aspx)

The SQL Server Working Set Message

(http://blogs.msdn.com/b/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx)

Troubleshooting Performance Problems in SQL Server 2008

(http://msdn.microsoft.com/en-us/library/dd672789(v=SQL.100).aspx)

Service pack information for SQL Server 2008

To resolve this problem, obtain the latest service pack for SQL Server 2008. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

968382

(http://support.microsoft.com/kb/968382/en-US/ )

How to obtain the latest service pack for SQL Server 2008

Service pack information for SQL Server 2008 R2

To resolve this problem, obtain the latest service pack for SQL Server 2008 R2. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2527041

(http://support.microsoft.com/kb/2527041/ )

How to obtain the latest service pack for SQL Server 2008 R2

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

This problem was first corrected in SQL Server 2008 Service Pack 2 for SQL Server 2008.
This problem was first corrected in SQL Server 2008 R2 Service Pack 1 for SQL Server 2008 R2.

Back to the top | Give Feedback