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.
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.
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.
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.
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.
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
.
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).
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
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
MSSQLWIKI 