Search This Blog

Saturday, February 9, 2008

SQL Server memory and CLR assemblies

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.
or
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 ...

4 comments:

J said...

Can you elaborate on the last line of your post?

"Never , use CLR based assemblies on a Virtual server"

We have a client that insists on hosting our 150GB db (OLTP/DSS with heavy clr use) on VMWare and we are experiencing issues...many issues, but lately issues dealing with clr appdomain unloads/out of memory issues.

Thanks in advance!

Abhay said...

Hi ,

In your case most probably the platform is x86 ...So you have a 2 GB VM limitation .Here CLR will be eating up VM from M2L which is by default 384 MB ...Most probably the defaullt that it needs is 100 MB , which is quite high ..

Now , the reason i requested not to use CLR in virtual enviornment is because there is not only VM limitation but also CPU , Physical memory limitation .

Anyway , the solution in your case is to move to 64 bit platform and then try CLR on VMWARE as this is not the physical memory but virtual memory pressure ..On 64 bit you have VAST VM for each process ..

HTH
Abhay

J said...

Actually, we are already on x64 and vmware and we keep getting "appdomain unloading due to memory pressure" errors. We have already applied SQL 2005 SP3, but no luck.

I think we are just hitting a memory ceiling. Currently have 16GB RAM.

Any other ideas?

Abhay said...

Thats interesting .So this might be a physical memory pressure because virtual memory pressure is not at all possible on x64 .Can you tell me how much memory SQL Server instance been allocated for the one in question and how much does it uses [you need to check perfmon Target ans total server memory during the high load time ].You might want to also try and find out if memory is being consumed by some other resources .
Are you also getting 17883,17884 , 701 etc ...any dumps being generated ..

If possible send me the data through FTP .

You can mail me data at hi_abhay78@yahoo.co.in or abhayc@microsoft.com if the zip file is small ..

we might need some optimization but i need to see my friend ...this looks strange if happening on x64 ...
Regards alwys ..
Abhay