Posted by: Denny Cherry
CLR, SQL CLR Procedure, SQL Server, SQL Server 2012
A change was made to the SQLCLR, as to when the SQLCLR is loaded. In SQL Server 2008 R2 and below the SQLCLR component was loaded only when the “clr enabled” setting, which is controlled through sp_configure, is enabled. Even with the SQLCLR is enabled on SQL Server 2008 R2 and below, the SQLCLR is only loaded into SQL Server’s memory when it is needed.
In SQL Server 2012 however SQLCLR is loaded on startup no matter if the “clr enabled” setting is disabled or not. This can be seen in the SQL Server ERRORLOG file by looking for the line which looks similar to the following.
Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.’
There are a couple of reasons that Microsoft wants the SQLCLR to be loaded automatically when the SQL Server starts instead of waiting for permission to load it.
The first is that there are plenty of native features which require the use of the SQLCLR in SQL Server 2012. This includes features like the geometry data type, the hierarchyid data type, etc.
The second is that by loading the SQLCLR when the SQL Server instance first starts this decreases the time that SQL Server must wait the first time that the SQLCLR is used. This is because the SQLCLR is a rather large package which must be loaded, when loading the SQLCLR the first time it is used can take up to several seconds to load the CLR into memory for use.
Just because the SQLCLR is loaded into memory when the SQL Server instance is first started doesn’t mean that the sp_configure setting “clr enabled” is useless. With this setting disabled, the SQL Server instance will not allow for the creation of CLR objects. Once the “clr enabled” setting is enabled the CLR doesn’t need to be loaded, but the SQL Server instance will now allow the user created assemblies to be created within the SQL Server instance.