SQL Server Performance

Memory Pressure in MemtoLeave

Discussion in 'Performance Tuning for DBAs' started by dosberg, Feb 3, 2005.

  1. dosberg New Member

    Starting in early 2004 our activeactive SQL Server cluster started experiencing intermittent slow downs, mini-dumps, and error messages in the client and SQL logs (see below). These symptoms would build until the system became non-responsive. The final symptom was login failures for connections using Windows authentication. Based on the error messages and that a restartfailover brought the server back online we were sure the problem was memory pressure.

    The first few times the memory pressure happened we applied standard troubleshooting. All the latest patches were applied, called PSS, code was reviewed, questionable processes and troublesome jobs were reviewed and cleaned up. Three or four times we were confident that we found the likely cause. A few weeks later the problem would re-occur.

    After six months and out of answers we bought Essential Support from Microsoft and opened a case The level of support and knowledge is much higher than PSS. Working with Essential Support they confirmed our in-house diagnosis of memory pressure in the MemToLeave area of the SQL Server memory space. For more information on MemToLeave find Ken Henderson#%92s article on MSDN.

    It took memory dumps and diagnostics after three incidents before Microsoft found the problem. There is memory leak in SQL Server and how it handles disconnects in remote queries across linked servers.

    The scenario in my shop is like this: an app server starts a request on behalf of a remote client. The request starts a remote query on SQL Server A that reaches across to SQL Server B. Our app server has aggressive retry logic (long story) so it terminates the original process if it has waited too long. SQL Server A handles the disconnect fine but SQL Server B doesn#%92t. It has no place to send the data it has gathered so that memory allocation on Server B leaks. It is not de-allocated until the next time SQL Server restarts.

    MemToLeave and the processes that run there want large chunks of contiguous memory. These leaked sections of memory eventually caused MemToLeave to become fragmented; leading to the infamous unable to reserve memory message in my SQL Server log.

    I#%92m guessing that someone will post a reply saying SQL Server doesn#%92t leak; it is our app server etc. Well, SQL Server does have a leak; a hotfix and KB article will be coming to a SQL Server near you soon.

    Here is what I would recommend for troubleshooting this process if you get any of the errors or symptoms I#%92ve listed:

    1. Read Ken Henderson#%92s article on MemToLeave so you understand this poorly documented part of SQL Server.
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
    2. Set the –G switch in your startup parameter to 384 megs. This will increase MemtoLeave but reduce the amount of memory available to the buffer pool.
    3. Look for any memory leaks in your own code. Review all use of Sp_OA, third party or in-house extended stored procedures etc. Remember every byte counts!
    4. Try to reduce your linked server traffic or identify bottlenecks in your remote queries.
    5. Bypass PSS and spend the money for Essential Support.
    6. If you suspect your problem is like mine changing this registry setting. It is the workaround that we are using until the hotfix is released.

    [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Serveryour instance nameProvidersSQLOLEDB]"AllowInProcess"=dword:00000000


    Error messages related to problem:
    ***Unable to get thread context for spid 56
    2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of dbghelp.dll - Not enough storage is available to process this command.

    2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only administrators may connect at this time.

    2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous memory of Size= 131072.

    2004-08-12 14:57:04.98 server SQL Server could not spawn process_loginread thread.


    THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE INFORMATION AND ADVICE AT YOUR OWN RISK.
  2. satya Moderator

    To work around this issue, disable Network Library protocol encryption by clicking to clear the Force Protocol Encryption check box in the Server network utility and/or the Client network utility.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. cchall New Member

    We have seen this kind of error intermittenly as well. Not sure how we are supposed to Disable Network Library protocol encryption.....? What tool or option are you using to do this?
  4. Luis Martin Moderator

    Microsoft SQL Server Tools.
    Server network utility and/or the Client network utility

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  5. dosberg New Member

    The issue didn't involve network protocal encryption, it was a memory leak in Sql Server. Microsoft included the hotfix is SP 4 which had 4 other hotfixes for memory leaks. If you go to the MS knowledge base and search for the spawn thread error you'll find the KB article.

    If your having this problem, I would QA SP 4 for your servers and deploy it if don't find any issues.


    Good luck
  6. satya Moderator

    Thanks and I appreciate your feedback on posting the resolution.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. regu_r New Member

    quote:Originally posted by dosberg

    Starting in early 2004 our activeactive SQL Server cluster started experiencing intermittent slow downs, mini-dumps, and error messages in the client and SQL logs (see below). These symptoms would build until the system became non-responsive. The final symptom was login failures for connections using Windows authentication. Based on the error messages and that a restartfailover brought the server back online we were sure the problem was memory pressure.

    The first few times the memory pressure happened we applied standard troubleshooting. All the latest patches were applied, called PSS, code was reviewed, questionable processes and troublesome jobs were reviewed and cleaned up. Three or four times we were confident that we found the likely cause. A few weeks later the problem would re-occur.

    After six months and out of answers we bought Essential Support from Microsoft and opened a case The level of support and knowledge is much higher than PSS. Working with Essential Support they confirmed our in-house diagnosis of memory pressure in the MemToLeave area of the SQL Server memory space. For more information on MemToLeave find Ken Henderson#%92s article on MSDN.

    It took memory dumps and diagnostics after three incidents before Microsoft found the problem. There is memory leak in SQL Server and how it handles disconnects in remote queries across linked servers.

    The scenario in my shop is like this: an app server starts a request on behalf of a remote client. The request starts a remote query on SQL Server A that reaches across to SQL Server B. Our app server has aggressive retry logic (long story) so it terminates the original process if it has waited too long. SQL Server A handles the disconnect fine but SQL Server B doesn#%92t. It has no place to send the data it has gathered so that memory allocation on Server B leaks. It is not de-allocated until the next time SQL Server restarts.

    MemToLeave and the processes that run there want large chunks of contiguous memory. These leaked sections of memory eventually caused MemToLeave to become fragmented; leading to the infamous unable to reserve memory message in my SQL Server log.

    I#%92m guessing that someone will post a reply saying SQL Server doesn#%92t leak; it is our app server etc. Well, SQL Server does have a leak; a hotfix and KB article will be coming to a SQL Server near you soon.

    Here is what I would recommend for troubleshooting this process if you get any of the errors or symptoms I#%92ve listed:

    1. Read Ken Henderson#%92s article on MemToLeave so you understand this poorly documented part of SQL Server.
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
    2. Set the –G switch in your startup parameter to 384 megs. This will increase MemtoLeave but reduce the amount of memory available to the buffer pool.
    3. Look for any memory leaks in your own code. Review all use of Sp_OA, third party or in-house extended stored procedures etc. Remember every byte counts!
    4. Try to reduce your linked server traffic or identify bottlenecks in your remote queries.
    5. Bypass PSS and spend the money for Essential Support.
    6. If you suspect your problem is like mine changing this registry setting. It is the workaround that we are using until the hotfix is released.

    [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Serveryour instance nameProvidersSQLOLEDB]"AllowInProcess"=dword:00000000


    Error messages related to problem:
    ***Unable to get thread context for spid 56
    2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of dbghelp.dll - Not enough storage is available to process this command.

    2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only administrators may connect at this time.

    2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous memory of Size= 131072.

    2004-08-12 14:57:04.98 server SQL Server could not spawn process_loginread thread.


    THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE INFORMATION AND ADVICE AT YOUR OWN RISK.


    Senior consultant,
    Capgemini
    Bangalore
  8. akothand New Member

    That is a great article by Ken Henderson. .learnt a lot!
    Thanks for sharing..

    Thanks,
    Anand.K

    Purity, patience, and perseverance are the three essentials to success
    and, above all, love - Swami Vivekananda
  9. derrickleggett New Member

    dosberg is right on this. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> We had a few hotfixes from MS that addressed this issue with us. We had a few issues causing it:<br /><br />1. We were using linked servers a lot with the same type of logic he is talking about. We started copying over tables periodically to eliminate as much of this as we could.<br /><br />2. We were using a third-party backup program. Because SQL Server doesn't play well with other things using the memory space, you get a lot of pressure in the MemtoLeave and paging file arenas. If you aren't careful, you can find SQL Server with a shortage in the MemtoLeave area either through a memory leak in the processing space outside of SQL Server (such as extended stored procedures or OA procs or through a lack of ability to kindly share memory which can result in abuse of the paging, swap, and memtoleave areas. <br /><br />It's pretty hard to get support for some of these things just because they really are difficult to diagnose. We had an MS engineering team looking at our problem and working with the third-party company for almost three weeks.<br /><br />Just an fyi. <br /><br />Read the SP3 and SP4 releases for memory leak issues and cursor issues. It's a fun read. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  10. KellyLeia New Member

    Just on the off chance someone is still paying attention to this thread...what was the final solution and/or service packs/hotfixes? (I know we don't have SP4, but hotfixes i'd have to check into). I'm running into this error, and i'm going to remove all direct queries to linked servers, modify (and eventually remove) any use of OA stored procedures (I don't like the previous DBA), and add a weekly reboot of the server. I'd love to be able to tell my boss there's a fix.

    Kelly
  11. satya Moderator

    Have you followed whatever referred above to avoid this error?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  12. dosberg New Member

    The KB article is 893312 and there is a hotfix for this. I think it just made in SP 4 if not it should be in the 2187 rollup. SP 4 has 6 or 7 other fixes for memory leaks so you should really consider going to that.

    Good luck
  13. satya Moderator

    But don't forget to apply hotfix on SP4, otherwise it will lead to another problem [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  14. MohammedU New Member

    I still have the same issue after applying sp4+rollup patch and after increasing the MemToLeave memory to 512MB with G switch...on a server with 20 GB memory <br /><br />Working with Microsoft and they are asking us to run the sqldump when the issue occurs...<br /><br />Good luck<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br /><br /><br />Mohammed U.
  15. satya Moderator

    MemToLeave value is limited to 512 MB only and they normally suggest in very rare cases to increase it from 256 mb.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  16. satya Moderator

    The MemToLeave value is limited to 512 MB only and they normally suggest in very rare cases to increase it from 256 mb.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  17. torpenhow New Member

  18. satya Moderator

    Welcome to the forums.
    Appreciate your feedback here and there has been many changes since the OP posted the question and relevant blog too [:)]/.

Share This Page