SQLCLR and system functionality in SQL Server 2008 – part 3

So, Microsoft.SqlServer.Types lives in the resource database and runs in its own appdomain when its used by system functions, like SELECT * FROM sys.assemblies. Let's try an experiment with the following setup.

I have two user assemblies in a database named "test". One doesn't access any .NET types, its called datetest. The new DATE/TIME-related data type series are not .NET-based, but you can use them in SQLCLR procs, as you can use NVARCHAR data type. There are some restrictions on DATE/TIME series, more about that later. My second user assembly is named hiertest. It uses the HierarchyID data type in SQLCLR code. That's (of course) OK too. Both my user assemblies are owned by DBO. Both catalog as SAFE_ACCESS.  There are no user assemblies in pubs database. Turn on SQL Profiler.

In pubs: SELECT * FROM sys.assemblies;

In profiler:
Assembly Load event:
Assembly Load Failed for requested assembly system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86 with Error code: 0x80070002
In SQL Server Log:
 Common language runtime (CLR) functionality initialized using…
 AppDomain 2 (32767.sys[runtime].1) created
   
Still in pubs: declare @h hierarchyid; select HierarchyID::GetRoot(); — invoke static method of hierarchyid data type

In profiler:
 Assembly Load Succeeded for Microsoft.SqlTypes.Types
In SQL Server Log:
 Unsafe assembly 'microsoft.sqlserver.types…" loaded into AppDomain 2 (32767.sys[runtime].1)

USE test
GO

In test: SELECT * FROM sys.assemblies;
In SQL Server log:
Assembly Load Failed for requested assembly system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86 with Error code: 0x80070002
  
In test: EXEC dbo.somedateproc — my SQLCLR proc that uses date.
In SQL Server log:
 AppDomain 3 (test.dbo[runtime].2) created
In profiler:
 Assembly Load Succeeded for datetimetest
  
In test: SELECT * FROM dbo.AncestorAndSelf('/'); — my SQLCLR UDF that uses HierarchyID
In SQL Server log:
 Unsafe assembly 'microsoft.sqlserver.types…" loaded into AppDomain 3 (test.dbo[runtime].2)
In profiler:
 Assembly Load Succeeded for Microsoft.SqlTypes.Types
 Assembly Load Succeeded for hiertest

So what happened??

There is only one resource database appdomain, it gets created when system functions (sys.assemblies) use system assemblies. The actual assembly that contains the system .NET data types loads only when it's required, into the resource database's appdomain. By the way, the declaration of a (NULL) hierarchyID variable isn't enough to load the assembly, you must actually use the variable. If you use only TSQL and the new data types, only this one appdomain is needed, regardless of how many different databases use them.

Additonal appdomains are created on a per-database, per-assembly owner basis as in SQL Server 2005. Each "user" appdomain will also load the system assembly Microsoft.SqlTypes.Types, if and only if it needs it. That is, if you use HierarchyID (or Geometry/Geography) in a SQLCLR procedure.

One last bit. Why are the appdomains referred to as: "AppDomain 2 (32767.sys[runtime].1)" and what happened to AppDomain 1? When .NET is loading into any executing process, there is a single appdomain created, the default appdomain. AppDomain 1. SQL Server doesn't load Microsoft.SqlServer.Types into this default appdomain, but starts another for the resource database. That's AppDomain 2 (into the process). The ".1" in "32767.sys[runtime].1" is first user appdomain.

Database administrators like to know about everything going on in "their" database, and with good reason…if the database fails (or even runs slowly) its (s)he who gets the first phone call for help. SQLCLR is still relatively new. Hope this was helpful in explaining what's going on. But remember, the exact appdomain implementation could be refined further in later releases. Cheers.

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.