Per-appdomain diagnostic info in SQL Server 2012 SQLCLR

In SQL Server 2012, SQLCLR now loads .NET 4.0 code rather than .NET 2.0 as in previous versions of SQL Server.

Note that the "main system" assemblies (e.g. mscorlib.dll, System.Data.dll) in .NET did not appear as a separate directory, between version 2.0 and 4.0; instead, the .NET 2.0 librares were updated. As an example, SQL Server 2008 loads the .NET 3.5 SP1 versions of the "main system" assemblies, but these are numbered as version 2.0.50727.xxxx. On my machine the 2.0 version of mscorlib.dll is actually version 2.0.50727.5420. The "original" version 2.0 mscorlib.dll (that shipped with SQL Server 2005 RTM) was 2.0.50727.42. See one of my previous blog posts for more information on .NET versioning in general. Even through this post references clients, the same versioning (in .NET 2.0-3.5) applies to SQLCLR as well.

Moving SQLCLR up to version 4.0 allows collection of some additional per-appdomain diagnostic information. In SQL Server 2012, this information appears in 3 additional columns in the DMV sys.dm_clr_appdomains. You can now monitor, on a per-appdomain basis, total_processor_time_ms, total_allocated_memory_kb (all memory allocations made since the appdomain was started, without subtracting garbage-collected memory) and survived_memory_kb (memory that survived the last full blocking garbage collection, referenced by the current appdomain).

I've been looking at these and gotten one particularly interesting result, so far. The master.sys[runtime].1 appdomain (the one where spatial data types and other system CLR items run) shows total_allocated_memory_kb = 0 and survived_memory_kb = 356 immediately after SQL Server 2012 starts up. It looks odd to have "surviving" memory for an appdomain that hasn't reported any allocated; I'll have to look more closely at the allocations to figure out how that could be.

Running the following code:
declare @a geometry = 'POINT(1 1)';

loads the spatial library (Microsoft.SqlServer.Types.dll) and results in a more believable total_allocated_memory_kb = 543, survived_memory_kb = 356.

This additional information should be helpful for DBAs and developers troubleshooting SQLCLR behaviors.


Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.