Memory Profiling SQL Server

NOTE: This article was originally published as a post in the .NET and Memory blog (by Andreas Suurkuusk, co-founder of SciTech Software AB).

This article is related to .NET Memory Profiler 3.x. .NET Memory Profiler 4 does not include a "Profile SQL Server" option, but it is no longer necessary to add the instrumentation assembly to SQL server. For .NET Memory Profiler 4 and later, just follow steps 1-6 in the beginning of the article.

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.