locked
Suspected Memory Leak - how to identify it in SQL 2005 RRS feed

  • Question

  • I have a SQL 2005 server which is not using AWE and has the default min/max memory settings of 0 and 2GB respectively.  The Server will run for 1-2 weeks and then become totally unresponsive.  The SQL Server error log has nasty messages in it like:

    10/06/2009 00:17:11,Logon,Unknown,SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: 10.9.198.53]
    10/06/2009 00:17:11,Logon,Unknown,Error: 17189<c/> Severity: 16<c/> State: 1.

    and

    10/06/2009 05:30:00,spid63,Unknown,There is insufficient system memory to run this query.
    10/06/2009 05:30:00,spid63,Unknown,Error: 701<c/> Severity: 17<c/> State: 123.

    I also noticed this error 1 day before the above messages.

    10/05/2009 00:16:52,Server,Unknown,Resource Monitor (0x7ac) Worker 0x03D1C0E8 appears to be non-yielding on Node 0. Memory freed: 39400 KB. Approx CPU Used: kernel 15 ms<c/> user 0 ms<c/> Interval: 65000.
    10/05/2009 00:16:52,Server,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
    10/05/2009 00:16:29,Server,Unknown,Stack Signature for the dump is 0x00000379
    10/05/2009 00:16:28,Server,Unknown,* Short Stack Dump
    10/05/2009 00:16:28,Server,Unknown,* -------------------------------------------------------------------------------
    10/05/2009 00:16:28,Server,Unknown,* *******************************************************************************
    10/05/2009 00:16:28,Server,Unknown,*
    10/05/2009 00:16:28,Server,Unknown,* Non-yielding Resource Monitor
    10/05/2009 00:16:28,Server,Unknown,*
    10/05/2009 00:16:28,Server,Unknown,*   10/05/09 00:16:28 spid 0
    10/05/2009 00:16:28,Server,Unknown,* BEGIN STACK DUMP:
    10/05/2009 00:16:28,Server,Unknown,*
    10/05/2009 00:16:28,Server,Unknown,* *******************************************************************************
    10/05/2009 00:16:28,Server,Unknown,***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0009.txt
    10/05/2009 00:16:28,Server,Unknown,**Dump thread - spid = 0<c/> PSS = 0x00000000<c/> EC = 0x00000000
    10/05/2009 00:16:26,Server,Unknown,Using 'dbghelp.dll' version '4.0.5'


    I suspect a memory leak is at work here, but it is difficult to track down what resource is actually leaking.

    I have since turned on SQL Profiling and a Perfmon log to find out more about what is happening in the lead up to the failure, but the failure repeats like clockwork.

    Suggestions would be greatly appreciated.

    Thx

    Thursday, October 15, 2009 8:09 PM

Answers

  • Most suspected memory leaks are not memory leaks but just the default behaviour on installations left on default memory configuration settings.
    The default max server memory setting (of 2147483647) is in MB, so is a bit more than 2GB.

    I'd start narrowing down the issue by installing SP3 (if it's not already installed) as this resolved a few memory leaks and by looking at the Memory Bottlenecks section of the 2005 P&T guide.


    ajmer dhariwal || eraofdata.com
    Thursday, October 15, 2009 8:42 PM
    Answerer

All replies

  • Most suspected memory leaks are not memory leaks but just the default behaviour on installations left on default memory configuration settings.
    The default max server memory setting (of 2147483647) is in MB, so is a bit more than 2GB.

    I'd start narrowing down the issue by installing SP3 (if it's not already installed) as this resolved a few memory leaks and by looking at the Memory Bottlenecks section of the 2005 P&T guide.


    ajmer dhariwal || eraofdata.com
    Thursday, October 15, 2009 8:42 PM
    Answerer
  • I have a SQL 2005 server which is not using AWE and has the default min/max memory settings of 0 and 2GB respectively.  The Server will run for 1-2 weeks and then become totally unresponsive.  The SQL Server error log has nasty messages in it like:

    10/06/2009 00:17:11,Logon,Unknown,SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: 10.9.198.53]
    10/06/2009 00:17:11,Logon,Unknown,Error: 17189<c/> Severity: 16<c/> State: 1.


    Check and see if you have a .NET application that is connecting with a network packet size of 8KB which requires multi-page allocations from VAS.  If you do, change the connection string to include (Packet Size=4096)

    http://support.microsoft.com/kb/903002

    select s.session_id, s.host_name, s.host_process_id, s.program_name, c.net_packet_size 
    from sys.dm_exec_connections c
    join sys.dm_exec_sessions s on c.session_id = s.session_id
    where net_packet_size >= 8000

    If you have rows over 8000 bytes you are running risk for VAS problems which is what your error message says.  Do you use Linked Servers, Extended Stored Procedures, SQLCLR, a COM/OLE Automation routine, or XML prepared documents in your code?  These are all VAS consumers.  Also do you use stored procedures or do you have a non-parameterized adhoc workload?  This will cause plan cache bloat and can lead to over commitment of the VAS.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, October 16, 2009 1:17 AM