MSSQLWIKI

Karthick P.K on SQL Server

Debugging memory Leaks using Debug diagnostic tool.

Posted by Karthick P.K on December 6, 2012

In my previous post (SQL Server memory leak ) I explained how to identify the modules  which are leaking the memory using ‘!heap’ commands.  Sometimes we may not be able to find the cause by displaying the memory using ‘!d’ commands to find the patterns or using search memory commands (s).

In such scenarios  we can use  Debug Diagnostic Tool or UMDH to track memory leaks.   This blog will explain how to identify the memory leaks using Debug diagnostics tools.

 

Download and install Debug Diagnostic Tool from http://www.microsoft.com/en-us/download/details.aspx?id=26798

 

1. Go to ToolsàOptions ->Preferences àSelect Record call stacks immediately when monitoring the leaks.

 

clip_image002

 

2. Go to the rules tab and select add rule.

 

3. Choose Native (non .Net) memory leak and handle leak.

 

4. Select the SQL Server or any process which has to be tracked for memory leak.

 

5. Click next and leave the default options (you can choose auto-unload Leak track when rule is completed or deactivated).

 

6. Click next and Activate the rule now.

 

7. Leaktrack.dll would have loaded to the process for which we are tracking the allocations.

 

8.  Now you can wait for the leak to happen again.

 

{

–If you are learning how to troubleshoot SQL Server memory leak follow the steps which we followed in previous post (https://mssqlwiki.com/2012/12/04/sql-server-memory-leak/)to leak the memory.

 

–Download HeapLeak.dll from this link.

–Create  an extended stored procedure in SQL Server

sp_addextendedproc ‘HeapLeak’,‘C:\HeapLeakdll\HeapLeak.dll’

–Let us execute this Extended SP 30 times and leak memory.

exec HeapLeak

go 30

}

 

9. Once you suspect memory is leaked. Go to the rules and take a full user dump by right clicking the Leak rule.

 

clip_image004

 

 

10. After the dump is captured , go to the advanced analysis tab, Add data files and select the dump which we generated.

 

11. Go to ToolsàOptions ->set the symbol path for analysis. Default Microsoft symbol path is below.

 

srv*c:\Websymbols*http://msdl.microsoft.com/download/symbols;c:\Release

 

Important: Replace c:\Release with symbol path of dll’s which you have loaded in SQL Server (optional)

 

 

11. In the available analysis script select memory pressure analyzers (memory analysis.asp).

 

12. Click start analysis.

 

clip_image006

 

 

13. Analysis might take a while depending on time it takes to load the symbols. Once the analysis is completed it would generate and open a HTML report.

This HTML report is  stored in C:\Program Files\DebugDiag\Reports\ by default and can be used for later reference.

 

I have attached a sample report which I collected when leaking memory using heapleak.dll in This link. You can use it for reference.

 

 

Report generated by debug diagnostic tool memory pressure analyzer will have the analysis summary and below Table Of Contents

 

sqlservr.exe__…………dmp

   Virtual Memory Analysis Report

   Heap Analysis Report

   Leak Analysis Report

   Outstanding allocation summary

    Detailed module report (Memory)

    Detailed module report (Handles)

 

 

 

14.  Analysis summary is  good portion in the report to start with and would give the module which is leaking the memory. Look at the below report.

 

clip_image008

 

 

15. Report has clearly indicated HeapLeak.dll has 255 MB of outstanding allocations. In heapleak.dll “Sub“ is the function which allocated this memory at offset 23.

 

 

16. Look at the virtual memory summary. It gives complete picture about memory distribution in the virtual address space. In the below summary memory reserved is 1.57 GB which is normal in 32-Bit SQL Server, but native heaps is 272.94 MB which is not normal.

Look at the heap summary there are 50 heaps.

 

 

 

 

 

clip_image010

 

clip_image012

 

17. Now look at the Outstanding allocation summary. It gives top 10 modules by allocation count and allocation size. In below summary HeapLeak has 26,182 allocations with size of 255.6 MB.

Note: In this report it is HeapLeak but in real time it might be any module which is leaking the memory

.

 

clip_image014

 

18. You can  also look at detailed module report(Memory).  It gives the memory allocation from each module along with function  and source line which allocated the memory (If you set the symbols for all the modules loaded).

 

clip_image016

 

 

By now we are sure that sub function in HeapLeak.dll has allocated 255 MB in line number 14 and has not released. The report also gives you the callstack samples that show the code path when functions was doing allocations. Refer This sample HTML report file. 

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki , join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/ and post your SQL Server questions to get answered by experts.

Related posts:

Basics of SQL Server Memory Architecture

SQL Server 2012 Memory

Troubleshooting SQL Server Memory

A significant part of SQL Server process memory has been paged out

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

 

5 Responses to “Debugging memory Leaks using Debug diagnostic tool.”

  1. […] Debugging memory Leaks using Debug diagnostic tool. […]

  2. thanks for the awesome post! I have been hunting for stuff similar to
    this. ill be following your Feed so i dont miss out the good things!
    once again, magnificent web page please keep this up! Please pardon me if my english is not good.

  3. 网购 said

    Greetings! Very useful advice in this particular post!

    It’s the little changes that produce the largest changes. Thanks for sharing!

  4. […] Debugging memory Leaks using Debug diagnostic tool. […]

  5. Usually I do not read article on blogs, hoeever I wish to sayy that this write-up very compoelled me to check
    oout and do so! Your writing style has been amazed me.
    Thanks, very great post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: