‘Short Stack Dump’ memory issue 1

[andreis]

Hi, we have ‘Short Stack Dump’ memory issue in our production SQL Server 2005 SP2 on 64-bit Windows 2003 R2 (VMWare). SQL Server freezes for hours before resuming normal functioning. Per somebody’s advice, we set SQL Server max memory to 2048 MB (out of 4 GB of RAM). Now, we are increasing RAM to 6 GB. What would be the best setting for SQL Server max memory option? Should enable AWE? Go back to the default value?

 

[mrdenny]

No you don't need to enable AWE on your server. AWE doesn't do much on x64 machines. If the OS has 8 Gigs of RAM, you'll want to set SQL to about 6 Gigs of RAM.

Can you post the stack dump and I'll take a look at it.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[andreis]

Denny, this is from the SQL ERRORLOG:


2010-08-04 07:02:53.98 Server **Dump thread - spid = 0, PSS = 0x0000000000000000, EC = 0x0000000000000000
2010-08-04 07:02:54.04 Server ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0003.txt
2010-08-04 07:02:54.06 Server * *******************************************************************************
2010-08-04 07:02:54.06 Server *
2010-08-04 07:02:54.06 Server * BEGIN STACK DUMP:
2010-08-04 07:02:54.06 Server * 08/04/10 07:02:54 spid 0
2010-08-04 07:02:54.06 Server *
2010-08-04 07:02:54.06 Server * Non-yielding Resource Monitor
2010-08-04 07:02:54.06 Server *
2010-08-04 07:02:54.06 Server * *******************************************************************************
2010-08-04 07:02:54.06 Server * -------------------------------------------------------------------------------
2010-08-04 07:02:54.06 Server * Short Stack Dump
2010-08-04 07:02:54.37 Server Stack Signature for the dump is 0x0000000000000205
2010-08-04 07:02:59.95 Server External dump process return code 0x20000001.
External dump process returned no errors.

2010-08-04 07:02:59.95 Server Resource Monitor (0x730) Worker 0x000000008000C1C0 appears to be non-yielding on Node 0. Memory freed: 63904 KB. Approx CPU Used: kernel 296 ms, user 203 ms, Interval: 65000.
2010-08-04 07:05:58.51 Backup Database backed up. Database: EHMC_OR_Archive, creation date(time): 2010/02/15(23:24:34), pages dumped: 573800, first LSN: 2302578:122:127, last LSN: 2302578:204:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_D0F970CE-584A-4063-83E6-1CE70639007B'}). This is an informational message only. No user action is required.
2010-08-04 07:07:36.70 Backup Database backed up. Database: EHMC_OR_EMPI, creation date(time): 2009/12/22(05:12:54), pages dumped: 22760, first LSN: 3496:4580:169, last LSN: 3496:4656:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_A6612EBE-FD11-4EC1-B371-62A70C7D1C62'}). This is an informational message only. No user action is required.
2010-08-04 07:09:11.24 Backup Database backed up. Database: Optilink, creation date(time): 2009/08/06(10:35:56), pages dumped: 348784, first LSN: 288741:3401:140, last LSN: 288741:3564:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_3098BDA0-EE20-44D7-A474-FF256FC8E651'}). This is an informational message only. No user action is required.
2010-08-04 07:16:24.95 Server Resource Monitor (0x730) Worker 0x000000008000C1C0 appears to be non-yielding on Node 0. Memory freed: 54736 KB. Approx CPU Used: kernel 281 ms, user 46 ms, Interval: 65000.

And this is dump txt file:
=====================================================================
BugCheck Dump
=====================================================================

This file is generated by Microsoft SQL Server
version 9.00.3042.00
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.


Computer type is AT/AT COMPATIBLE.
Current time is 07:02:53 08/04/10.
2 Unknown CPU 9., 2401 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 2.

Memory
MemoryLoad = 96%
Total Physical = 4095 MB
Available Physical = 158 MB
Total Page File = 5878 MB
Available Page File = 987 MB
Total Virtual = 8388607 MB
Available Virtual = 8384008 MB
**Dump thread - spid = 0, PSS = 0x0000000000000000, EC = 0x0000000000000000
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0003.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 08/04/10 07:02:54 spid 0
*
* Non-yielding Resource Monitor
*
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump

 

[mrdenny]

The memory settings won't fix this. The resource monitor process is non-yielding which means that it isn't responding correctly to commands that are being sent from the SQL engine.

When this happens you can either weight the problem out, or restart the instance.

Try installing the latest CU, and if that doesn't resolve the problem open a ticket with Microsoft support.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[SQLBill]

I've seen this response several places in response to that error:

Resource monitor is a component on SQL Server that keeps track available of resources in the machine, e.g. memory. If memory is low, for example, it notifies other components to release memory (e.g. plan cache). When the server detects that the resource monitor has not made progress for some time, it flags it as a bad condition and creates a dump to allow further investigation. This usually happens when the server is under heavy load or is paging heavily.

I've seen responses that this is known issue in SQL Server 2005 and that SP2 plus the latest cumulative SP will resolve the issue. You might try this one if you haven't loaded it already:

http://support.microsoft.com/kb/949095/en-us

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875