CLR error when compiling a stored procedure (on some systems)

Question

Post reply

CLR error when compiling a stored procedure (on some systems)

StripedCat

SSChasing Mays

Points: 614
More actions
July 21, 2009 at 12:17 pm

#208498

My company installs our software on our customers' systems, compiling various SQL objects. On some of the systems (maybe 1 out of 50) an error occurred while compiling two stored procedures that each call a CLR function:
[font="Courier New"]Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005. You need to restart SQL server to use CLR integration features.[/font]
After the error occurred during the installation program, I manually tried compiling the procedures, but the same error occurred. After rebooting, without changing anything else, the procedures compiled without error.
I should mention that our customers do not have CLR enabled yet, but that doesn't seem to make a difference here. The procedures will be used in future releases; for now they are just getting compiled but not executed.
I'm new to CLR, but this seems like a memory issue(?)
Do I need to specifically allocate memory for CLR? How do I know how much memory CLR will need when our customers start using it?
On one of the systems that had the problem, I ran the query below. It returned a value of 168, which seems quite low.
[font="Courier New"]select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'[/font]
Any help or info would be appreciated. Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Take a look here:

http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx

Essentially - CLR uses memory external to SQL, so you may need to increase the "memtoleave" so that CLR has some room to work.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

It would be interesting to know if the issue is specific to 32 bit SQL Servers only or not. If it is specific to 32bit SQL Servers, then I would go with Matt and suspect a VAS/MTL pressure problem. That would point me to think that you are using unapproved assemblies possibly in your code which would require TRUSTWORTHY ON and UNSAFE assembly rights. You can read more about the VAS/MTL allocation in the following blog post:

Understanding the VAS Reservation (aka MemToLeave) in SQL Server

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Jonathan Kehayias (7/21/2009)
snip.. That would point me to think that you are using unapproved assemblies possibly in your code which would require TRUSTWORTHY ON and UNSAFE assembly rights.

My experience has been when this is true it usually tells you that you need that. Even if it doesn't the easiest way to check is to set it UNSAFE and try it. I'm really thinking the problem is memory, since memory pressure has some of the gretest effect on SQLCLR.

CEWII

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Elliott W (7/21/2009)
Jonathan Kehayias (7/21/2009)
snip.. That would point me to think that you are using unapproved assemblies possibly in your code which would require TRUSTWORTHY ON and UNSAFE assembly rights.
My experience has been when this is true it usually tells you that you need that. Even if it doesn't the easiest way to check is to set it UNSAFE and try it. I'm really thinking the problem is memory, since memory pressure has some of the gretest effect on SQLCLR.
CEWII

I think you misunderstood what I was saying there.

That it is consuming so much memory during Assembly creation is usually a sign that unapproved assemblies are being used. When you create the assembly, the dependencies are also loaded into the SQL Server, which consumes memory, and when you use unapproved assemblies, the memory footprint for assembly loading increases drastically as each of the unapproved assemblies loads and then its dependencies load. If you want to see what I mean, try and add System.Web to a SQL Server, and you will see it load a massive list of dependent assemblies as well, and on 32 bit servers that use VAS normally, the load will fail unless you restart the SQL Instance.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Jonathan Kehayias (7/21/2009)
I think you misunderstood what I was saying there.
That it is consuming so much memory during Assembly creation is usually a sign that unapproved assemblies are being used. When you create the assembly, the dependencies are also loaded into the SQL Server, which consumes memory, and when you use unapproved assemblies, the memory footprint for assembly loading increases drastically as each of the unapproved assemblies loads and then its dependencies load. If you want to see what I mean, try and add System.Web to a SQL Server, and you will see it load a massive list of dependent assemblies as well, and on 32 bit servers that use VAS normally, the load will fail unless you restart the SQL Instance.

I think you are getting hung up on the unapproved assemblies, approved or unapproved it takes a fair amount of memory to handle the load of the assembly. I do, however, agree that he is likely having a memory pressure issue, just not why..

CEWII

StripedCat SSChasing Mays Points: 614 More actions · Answer 6

I greatly appreciate the help that all of you have offered. I read Jonathan's article about Understanding the VAS Reservation which is very informative.

This might be a dumb question but I'll ask anyway - what is an "unapproved assembly"? The more detail the better!

Thanks

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

An unapproved assembly is basically an assembly that MS has not explicitly tested and stated that they believe should be referenced by a SQLCLR assembly. Most assemblies cannot be referenced unless they exist in the SQL Server (ie: CREATE ASSEMBLY), the approved assemblies don't have this requirement. I think this is where a previous poster has stated that to use an unapproved assembly uses more memory to bring it into the server. I am not convinced of that point, and would love to see some empirical data on it.. But beside that point, the approved assemblies cover a very wide range of functionality, so in MOST cases you won't have to use the others. There are cases of third-party assemblies that you might want/need to use, like sharpziplib, you have to load all of their dependencies as well.

There is a list of "approved" assemblies in BOL, unfortunately I don't have my regular computer right now to get the link for you..

I hope I haven't muddied the waters for you..

CEWII

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

Its really quite simple to show that an unapproved assembly drives up memory usage. First use a SQL Server that is isolated and has no other users or SQL CLR calls being made to it and restart it to clear the memory clerks. SQLCLR will only take about 8KB of memory when the system first loads. Take the script from my article:

http://www.sqlservercentral.com/articles/SQLCLR/65656/

and create the assembly. It only uses approved assemblies. Then check memory usage:

select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb

from sys.dm_os_memory_clerks

where type = 'MEMORYCLERK_SQLCLR'

It will only use around 8MB of memory. Now restart SQL Server again, and add the unapproved System.Web assembly:

CREATE ASSEMBLY SystemWeb

FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'

WITH PERMISSION_SET = UNSAFE

and do two things, watch what it does, and monitor memory. If you watch what it does, it loads the following additional assemblies which are dependent assemblies of System.Web:

Warning: The Microsoft .NET Framework assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.directoryservices.protocols, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.runtime.remoting, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.runtime.serialization.formatters.soap, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.design, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'accessibility, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.drawing.design, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.web.regularexpressions, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.serviceprocess, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.configuration.install, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

and memory usage to just add this assembly, not run any code associated with it is 14-16MB, but that would be in addition to the base assembly load cost memory wise, and any additional assemblies that have to load in association with the assembly. 24MB might seem small memory wise, but in reality, it actually isn't in the terms of VAS/MTL allocation inside of SQL Server, especially on 32bit systems. You could have 100MB of available VAS on your server, and if your largest contiguous allocation is 16MB (a sign of VAS fragmentation which is very common of 32bit servers with high VAS consumers ie, linked servers OLE Automation calls, SQLCLR, extended stored procedures) the most you can load is 16MB because VAS allocations inside SQL Server require contiguous address space to allocate memory. This is where your OOM exceptions occur loading unapproved assemblies. It is also why restarting a server can often allow this to succeed since the VAS is unfragmented and most likely to have larger blocks of contiguous space available immediately after starting.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

The list of approved assemblies can be found:

Supported .NET Framework Libraries