I will keep this short .First of all CLR and JVM are built upon same concepts :) ..merging of the CLR with SQL Server 2005 gives SQL Server the ability to create database objects using modern object-oriented languages like VB.NET and C#because CLR Objects support complex logic and have better computation capabilities.Thats the reason its use is increasing day by day ..
many a times i have seen cases where people say that they have memory pressure due to CLR objects ..we always feel that ( include me as well ) CLR objects / assemblies will only consume MemToLeave ..but it also consumes the buffer pool..butttt most SQL CLR memory allocations occur outside of the SQL Buffer Pool in MemToLeave area (384 MB by default)..
[ Mem2Leave= 255 worker threads x .5MB +256 MBStack size<--called as -g switch]
Now , There are two types of memory pressures
1) Physical memory pressure based on the amount of available system memory
2) Virtual Address Space (VAS : 2 gigs by default on 32 bit architecture)memory pressure based on the number of available virtual addresses.
Query sys.dm_os_memory_clerks dmv :
select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'
Here single_pages_kb is buffer pool and multi_pages_kb is MemToLeave area..the same and better output can be seen in DBCC Memorystatus as well.
Now,SQL CLR can’t use so much memory that it causes external physical memory pressure (Don't ask why :D).
So, SQL CLR will not be alowed to use more than 256 MB by default..
Now , if you are getting errors like :
Error: 701 , Severity: 17, State: 123.
There is insufficient system memory to run this query.
failed to reserve a contiguous space of 65536 K
first thing I would suggest is concentrate on the CLR Assemblies and see if we can optimize it
Other stuff we can do is increasing Mem2Leave area by using -g384 or -g512 in the startup parameters (if we see that the buffer pool is not heavily used)...further we can check if there is no harm using /3gb switch in the boot.ini file ....keep server activity confined to SQL Server , keep you database finely tuned by using more SPs , updating stats , defragmenting disks , rebuilding indexes .....
if nothing is helping you move to 64 bit machines :D.......thts the last option....
Never , use CLR based assemblies on a Virtual server ....
Your comments are welcome ...