locked
Memory Leak RRS feed

  • Question

  • Dear Friends,

    I read lot of article and could not get satisfied answer, what is memory leak is it means when sqlserver memory go beyond the maximum memory set for sqlserver. how we can find memory leak in sqlserver and what are the properties of memory leak in sqlserver.

    thank you.

    regards,


     


    asad

    Friday, June 26, 2015 5:45 PM

Answers

  • what is memory leak is

    Generally you won't see a memory leak in SQL Server. There have been a few rare bugs that have cropped up over the years, like the varbinary over linked server leak. If you run CLR, it's possible you may have a leak in the .NET code. It all depends and it's not necessarily ever a leak (unless it's JAVA) :) haha! 

    In some leak cases you will see ERROR 703 messages in the sql error log when a process is leaking.

    Do you have a specific scenario you're trying to troubleshoot? What version and build are you running? Have you installed the lasted SP + CU?

    For processes outside of SQL Server I'd use perfmon and review the PROCESS\PRIVATE BYTES counter to see if it continually grows, which would be an indicator of a leak but may also indicate the process is just being used more.

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Friday, June 26, 2015 7:22 PM
  • That is normal and expected behavior and does not indicate a memory leak in SQL Server.  Not all memory allocations are controlled by the "max server memory" setting and you have no control over those allocations.

    Please see:

    https://support.microsoft.com/en-us/kb/321363?wa=wsignin1.0

    Friday, June 26, 2015 6:01 PM
  • Dear Friends,

    I read lot of article and could not get satisfied answer, what is memory leak is it means when sqlserver memory go beyond the maximum memory set for sqlserver. how we can find memory leak in sqlserver and what are the properties of memory leak in sqlserver.

    Memory leak simply means that SQL Server component is not releasing memory when required or when its work is done or Taking memory when not required.

    If SQL Server is updated to latest SP and patches there is very very minimum chance that there would be memory leak

    From 2012 onwards all memory allocation for SQL Servr is done from max server memory setting value. Only some allocation for third party drivers and providers is done from outside max server memory value. CLR, XML and other stuff take memory from max server memory setting value.

    To find memory leak you have DMV

    Select * from sys.dm_os_memory_clerks
    
    Select * from sys.dm_os_memory_objects
    
    select * from sys.dm_os_memory_cache_counters


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Saturday, June 27, 2015 4:02 AM
  • thank you it was usefull, Please  in our case sqlserver 2012 with asp.net, how I can find  .net  CLR leak as well sqlserver using  TSQL.


    asad

    Is the ASP.NET app on the same server as SQL?

    If you're looking for leaks in ASP.NET then I would start by reviewing the w3wp process and viewing the following:

    • Process\Handle Count
    • Process\Thread Count
    • Process\Private Bytes
    • Process\Virtual Bytes

    You'll want to view these over time and see if they continuously grow. It's been awhile since I've dealt with IIS; but, in my past experience the IIS processes (w3wp) could grow to about 1.3 - 1.4 GB of usage before they needed to be restarted. You could see this in Private Bytes, which is committed memory (RAM + Page file).

    For SQL, patch it! There is a .0001% chance your SQL Server is leaking. :)

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Saturday, June 27, 2015 5:45 PM

All replies

  • That is normal and expected behavior and does not indicate a memory leak in SQL Server.  Not all memory allocations are controlled by the "max server memory" setting and you have no control over those allocations.

    Please see:

    https://support.microsoft.com/en-us/kb/321363?wa=wsignin1.0

    Friday, June 26, 2015 6:01 PM
  • what is memory leak is

    Generally you won't see a memory leak in SQL Server. There have been a few rare bugs that have cropped up over the years, like the varbinary over linked server leak. If you run CLR, it's possible you may have a leak in the .NET code. It all depends and it's not necessarily ever a leak (unless it's JAVA) :) haha! 

    In some leak cases you will see ERROR 703 messages in the sql error log when a process is leaking.

    Do you have a specific scenario you're trying to troubleshoot? What version and build are you running? Have you installed the lasted SP + CU?

    For processes outside of SQL Server I'd use perfmon and review the PROCESS\PRIVATE BYTES counter to see if it continually grows, which would be an indicator of a leak but may also indicate the process is just being used more.

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Friday, June 26, 2015 7:22 PM
  • thank you it was usefull, Please  in our case sqlserver 2012 with asp.net, how I can find  .net  CLR leak as well sqlserver using  TSQL.


    asad

    Friday, June 26, 2015 9:25 PM
  • As of SQL Server 2012, CLR memory is included in max server memory setting so I'd say it's something other than a CLR issue.

    Sql Server, SSAS & Azure Db Performance Tuning & Monitoring Software | www.minidba.com

    Friday, June 26, 2015 10:09 PM
  • Dear Friends,

    I read lot of article and could not get satisfied answer, what is memory leak is it means when sqlserver memory go beyond the maximum memory set for sqlserver. how we can find memory leak in sqlserver and what are the properties of memory leak in sqlserver.

    Memory leak simply means that SQL Server component is not releasing memory when required or when its work is done or Taking memory when not required.

    If SQL Server is updated to latest SP and patches there is very very minimum chance that there would be memory leak

    From 2012 onwards all memory allocation for SQL Servr is done from max server memory setting value. Only some allocation for third party drivers and providers is done from outside max server memory value. CLR, XML and other stuff take memory from max server memory setting value.

    To find memory leak you have DMV

    Select * from sys.dm_os_memory_clerks
    
    Select * from sys.dm_os_memory_objects
    
    select * from sys.dm_os_memory_cache_counters


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Saturday, June 27, 2015 4:02 AM
  • thank you it was usefull, Please  in our case sqlserver 2012 with asp.net, how I can find  .net  CLR leak as well sqlserver using  TSQL.


    asad

    Is the ASP.NET app on the same server as SQL?

    If you're looking for leaks in ASP.NET then I would start by reviewing the w3wp process and viewing the following:

    • Process\Handle Count
    • Process\Thread Count
    • Process\Private Bytes
    • Process\Virtual Bytes

    You'll want to view these over time and see if they continuously grow. It's been awhile since I've dealt with IIS; but, in my past experience the IIS processes (w3wp) could grow to about 1.3 - 1.4 GB of usage before they needed to be restarted. You could see this in Private Bytes, which is committed memory (RAM + Page file).

    For SQL, patch it! There is a .0001% chance your SQL Server is leaking. :)

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Saturday, June 27, 2015 5:45 PM
  • There is a CU which addressing memory leak apply the same.

    https://support.microsoft.com/en-us/kb/2894115


    Regards, Pradyothana DP. Please Mark This As Helpful if it helps to solve your issue. ========================================================== https://social.technet.microsoft.com/Profile/pradyothanadp http://www.dbainhouse.blogspot.in/

    Sunday, June 28, 2015 3:26 AM
  • Why do you think there is a "memory leak"?  What is the symptom you are seeing?  Are you receiving an error message?

    Please post the results of SELECT @@VERSION.

    Monday, June 29, 2015 11:53 AM