Thomas LaRock (8/18/2008)
we try to limit the use of CLR here, simply because it does not seem to be necessary. however, one caveat that i have seen has to do with memory. although I have been told that the CLR memory space will not affect the mssql memory space, what I have found is that periodically the CLR ememory space becomes so fragmented that a bounce of the service is necessary.
so, while you may be told that you do not have to worry about CLR bringing a server down, the fact is that if someone is using CLR and they rely on CLR for production, it may be necessary to bounce the services anyway. this results in a outage, plain and simple.
if possible, sit doen with the developers and ask questions, and explain some of the risks. chances are once they know the risks, they may think twice about using CLR.
This points to either a problem in your server configuration or a problem with your CLR code where it is not allowing the GC to activate and free the memory properly. I have had CLR running for many months with no appdomain unloads or server restarts necessary on any server that is running it. In a worst case scenario, you shouldn't have to restart the service, as a DBCC FREESYSTEMCACHE call will clear the CLR stack from its MTL allocations.
CLR runs in a very limited and finite memory space in SQL Server called the MemToLeave area. This is generally only allocated 256MB of memory at startup, depending on a number of factors to include how much memory your server has, network packet size, and various other configuration options. It isn't very hard for SQL Server to bump into MTL memory pressure without CLR running let alone with CLR running. I'd recommend that you investigate what is actually causing your problem in the sys.dm_os_memory_clerks and see where your memory is actually being allocated. XP's also work in the MTL area, which is why some tools like litespeed used to create MTL memory pressure. There is a lot more to play here than CLR causing a problem.
The above also applies to the post about CLR crashing a SQL Server. By design CLR won't do this unless your wrote code that leaks pointers in UNSAFE registered assemblies. If you build appropriate CLR code for use in SQL only for Instances where CLR is appropriate, and I admit that these are extremely limited, then you shouldn't have problems with running CLR in SQL. 99% of the CLR code that I have seen on this forum, as well as the others that I answer on is misplaced, and belongs in an application layer outside of SQL Server. 99% of String manipulations being done in CLR can be done in TSQL much faster, as Jeff says, regular expressions replacements and matching seem to be the only two that outperform a solid TSQL solution.