.NET and Memory

One profiling scenario that is currently not supported by .NET Memory Profiler is SQL Server profiling. If your SQL Server database contains any .NET stored procedures or any other .NET code it can be important to validate the memory usage of the .NET code, especially if any state is kept by the .NET code. As soon as any state information is kept in memory, there is a risk of memory leaks.

As the SQL Server is running as a Windows service, the Profile service command should be used, as described below:

1. Start .NET Memory Profiler as an adminstrator (e.g. right click and select “Run as administrator”)
2. Select Profile Service
3. Find the correct SQL Server service
4. Start profiling
5. The SQL Server service will be restarted and you will see the message “Waiting for profiled process to start”.
6. Make sure that the .NET runtime is loaded into the process, e.g. by executing a stored procedure. This should cause the message to disappear and the profiling to start.

Unfortunately, this will not work! You will soon receive an error message stating:

System.IO.FileNotFoundException: Could not load file or assembly ‘SciTech.NetMemProfiler.InstrumentationHelper2, Version=3.1.0.0, Culture=neutral, PublicKeyToken=2dc3dfad160eda83′ or one of its dependencies. The system cannot find the file specified. 

The problem is that the dispose tracker (which is usually enabled) requires a helper assembly. This assembly is installed in the GAC, but SQL Server will not load this assembly. The solution is to add the helper assembly to the database. But there’s one more gotcha. The helper assembly performs native functions calls, and thus requires unrestricted permissions, i.e. it must be added with PERMISSION_SET = UNSAFE. In order to do this, the database needs to be marked as “Trustworthy” (which is not recommended), or a database login with UNSAFE ASSEMBLY rights must be created for the helper assembly. The login can be created using the following SQL script:

USE master
GO
IF NOT EXISTS (SELECT name FROM master.sys.asymmetric_keys WHERE name = 'MemProfilerInstrumentationKey')
begin
  CREATE ASYMMETRIC KEY MemProfilerInstrumentationKey
    FROM EXECUTABLE FILE = 'C:\Program files\SciTech\NetMemProfiler3\Assemblies\SciTech.NetMemProfiler.InstrumentationHelper2.dll'
  CREATE LOGIN MemProfilerInstrumentationLogin
    FROM ASYMMETRIC KEY MemProfilerInstrumentationKey
  GRANT UNSAFE ASSEMBLY TO MemProfilerInstrumentationLogin
end 

After the login has been created, the assembly can be added with unrestricted permissions. It must be added to each database that includes .NET code that will be profiled, using the SQL script below.

USE [<Database name>]
CREATE ASSEMBLY [SciTech.NetMemProfiler.InstrumentationHelper2]
  FROM 'C:\Program files\SciTech\NetMemProfiler3\Assemblies\SciTech.NetMemProfiler.InstrumentationHelper2.dll'
  WITH PERMISSION_SET = UNSAFE;

 Now it should be possible to profile .NET code hosted by SQL Server. Just follow the steps from the beginning of this post.

Once you have finished profiling, the permissions granted to the profiler can be removed by dropping the assembly, login, and asymmetric key:

USE [<Database name>]
DROP ASSEMBLY [SciTech.NetMemProfiler.InstrumentationHelper2]
GO
USE master
DROP LOGIN MemProfilerInstrumentationLogin
DROP ASYMMETRIC KEY MemProfilerInstrumentationKey

Admittedly these requirements make it a bit cumbersome to profile SQL Server hosted .NET code. The next version of the profiler will include an option to “Profile SQL Server”, which will automatically prepare the database for profiling.

Thanks to Gerhard Obenaus at Blackbaud, Inc. for pointing out how to create a login for the helper assembly, instead of using the “Trustworthy” alternative.

A common question we get about the profiler is how the number presented under the “Other data-><Other>” node in the Native memory tree should be interpreted. This number can often be significant compared to the total memory used by the application.

The “Other data -> <Other>” node represents native memory that the profiler has failed to identify (as mentioned below, data collected by the unmanaged resource tracker is currently not used). This can be memory used by:

• Unmanaged resources
  Unmanaged windows resources such as HBITMAPs, HWNDs and HICONs etc. will consume memory, but the profiler will not be able to identify it.
• Internal memory used by the runtime
  The runtime needs to allocate memory for internal structures. These structures can contain information about loaded modules,  classes and other data needed by the runtime. When attaching, the profiler identifies some of this memory but when doing normal profiling this memory will be unidentified. Additionally, the amount of unidentified native memory is greater when running under the profiler than it is when running the application the normal way. The runtime itself probably consumes more memory when profiling since it cannot perform certain optimizations and it might keep information available for the profiler that could otherwise be discarded. 
• Memory allocated by VirtualAlloc
  If the memory is not sub-allocated by other functions, like HeapAlloc, then it will be presented as <Other>.
• Memory mapped files
  If the file mapping is created without a specified file, then the memory used will be unidentified.
• Committed stack memory
  This memory is allocated using VirtualAlloc and can consume quite a lot of memory if you have many threads in the application. I will discuss the stack memory usage in a future post.

What do I do if a have a high memory usage that has not been identified by the profiler?

First of all you should investigate whether the high memory usage is really a problem. As mentioned above, the amount of unidentified native memory is higher when running under the profiler. This increase can be mitigated by using the “Attach to” command in the profiler. When attaching to a process the profiler will not affect the memory usage of the profiled process at all (however, since it reads a lot of memory pages, it will affect the working set of the process when a snapshot is collected).

If you still see a lot of unidentified memory, and especially if the size of this memory increases, you can continue the investigation by using the unmanaged resources tracker. It can be used to collect more detailed information about the unmanaged memory usage of the application. After you have collected a snapshot, you will be able to see unmanaged resources in the Types/Resources view. Unfortunately, the information collected by the resource tracker is currently only presented in the normal snapshot views, the native memory tree view does not use the information collected by the resource tracker. This will be implemented in a future version.

Unmanaged resources are identified by the  icon in the Types/Resources view  and can be further investigated by double-clicking. Each created resource instance is associated with a creation context. When looking for a leak related to unmanaged resources, the instances associated with the unmanaged interop context () are usually of most interest. The reason for this is that the creation of these instances is normally performed by managed code (using P/Invoke calls) and it it easier to track why they’re created.  Resource instances associated with the “Managed runtime” and the “Other unmanaged” are usually created by the runtime or other unmanaged code, so it might not be possibly to identify why they’re created.

I hope this post has given you some useful information about the native memory view, and some directions on what to do if you have an application with a lot of unidentified native memory. We will continue to work on identifying native memory, and future versions of the profiler will include more detailed information.