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.

@bobbeauch

Other articles

Over 1000 XEvents in SQL Server 2016 CTP2. Here are the new ones.

Extended events has firmly established itself as the premier diagnostic feature in SQL Server and SQL Server 2016 brings along more events to correspond to

Taking the Azure SQL Database row-level security preview for a spin

The security announcements around Azure SQL Database keep coming. Auditing was implemented a few months ago, and today it was followed by a preview of

Azure SQL Database V12 Preview – Spatial Fully Functional

Yesterday’s blog post about Azure SQL Database V12 mentioned that one of the features I was particularly interested in seeing/testing were the spatial features. Interestingly,

Not a “me-too announcement” blog on Azure SQL Database V12 preview

In general, I usually hate “me too” announcement blog posts. Over the years, I’ve considered it less than useful to simply repeat “Product XXX released

AzureML: What components are used by the sample experiments?

A few months ago, I embarked on a project to learn more about data mining, machine learning and, as a prerequisite, statistics. I was tired

Book Review: Chris Webb’s “Power Query for Power BI and Excel”

I’ve been experimenting with Power Query and the rest of the Power BI suite in Excel (desktop edition) since it was originally released. But, like

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.