SQL Server with Mr. Denny

Jun 14 2012   9:00PM GMT

When does SQLCLR launch?

Denny Cherry Denny Cherry Profile: Denny Cherry

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.

Denny

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    [...] When does SQLCLR launch? - A brief look at SQLCLR behaviour changes in SQL 2012 courtesy of Denny Cherry (Blog|Twitter). [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: