CLR out of memory

I've got a CLR function that runs fine on my workstation running SQL
Server Express; however, it throws OOM exceptions on the full SQL
Server 2005.
The function is very simple: it takes an XML file and a stylesheet,
transforms the XML using the stylesheet, and returns the result as a
string. On my workstation, with 2 gb RAM (1gb allocated to SQL Server),
I hardly ever have any problem with it. On the server, with 4gb, the
same function running on the same data terminates with the following
message:
.NET Framework execution was aborted by escalation policy because of
out of memory.
System.Threading.ThreadAbortException: Thread was being aborted.
(...stack follows...)
This happens when input XML data size exceeds ~30-50 mb or so.
I checked performance counters and memory clerks. The problem seems to
be that CLR never grabs enough memory even when it should be available.
I ran the same request on the same data, first on my workstation, then
on the server. On the workstation, once the function was started, CLR
memory utilization went up to about 108 megs, and the function
completed normally. On the server, the CLR memory utilization only went
up to about 75 megs, then the function was terminated.
I checked command line parameters - the -g switch was not used at
either the server or the workstation. Just in case, I added the default
-g256 at the server, to no effect.
So, my question is - what could cause the CLR to not use available
memory? There's something wrong with the server configuration, but I
can't figure out what. Any help would be greatly appreciated!
Below is the function in question.
[SqlFunction(Name="_clrApplyStylesheet",
DataAccess=DataAccessKind.Read)]
public static SqlString _clrApplyStylesheet(SqlXml XmlData,
SqlXml XmlStylesheet)
{
XPathDocument stylesheet, xmlData;
XslCompiledTransform xTransform;
System.Text.StringBuilder sBuilder;
XmlWriter xWriter;
stylesheet = new
XPathDocument(XmlStylesheet.CreateReader());
xmlData = new XPathDocument(XmlData.CreateReader());
sBuilder = new System.Text.StringBuilder();
xWriter = XmlWriter.Create(sBuilder);
xTransform = new XslCompiledTransform();
xTransform.Load(stylesheet);
xTransform.Transform(xmlData, xWriter);
return sBuilder.ToString();
}Hi Abe,
I mentioned this problem briefly here:
http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx
The issue you are seeing is not because of a lack in physical memory but in
a lack of Virtual Address Space. If you have 1 GB allocated to SQL on your
workstation, then the SQL Buffer Pool will reserve 1 GB of VAS for its use,
leaving 1 GB for all the allocations made outside the buffer pool, including
CLR. However, as you found and contrary to what you expected, on your
server the Buffer Pool is able to use much more memory (depending on if you
are using AWE or /3GB) leaving only the default 256 MB of VAS for everything
else.
Unfortunately, I think your only real options would be to use the -g flag to
reserve more memory for allocations outside the buffer pool or, if possible,
use 64-bit hardware.
Steven
"Abe" <revres_lqs@.yahoo.com> wrote in message
news:1152829807.975377.50710@.35g2000cwc.googlegroups.com...
> I've got a CLR function that runs fine on my workstation running SQL
> Server Express; however, it throws OOM exceptions on the full SQL
> Server 2005.
> The function is very simple: it takes an XML file and a stylesheet,
> transforms the XML using the stylesheet, and returns the result as a
> string. On my workstation, with 2 gb RAM (1gb allocated to SQL Server),
> I hardly ever have any problem with it. On the server, with 4gb, the
> same function running on the same data terminates with the following
> message:
> .NET Framework execution was aborted by escalation policy because of
> out of memory.
> System.Threading.ThreadAbortException: Thread was being aborted.
> (...stack follows...)
> This happens when input XML data size exceeds ~30-50 mb or so.
> I checked performance counters and memory clerks. The problem seems to
> be that CLR never grabs enough memory even when it should be available.
> I ran the same request on the same data, first on my workstation, then
> on the server. On the workstation, once the function was started, CLR
> memory utilization went up to about 108 megs, and the function
> completed normally. On the server, the CLR memory utilization only went
> up to about 75 megs, then the function was terminated.
> I checked command line parameters - the -g switch was not used at
> either the server or the workstation. Just in case, I added the default
> -g256 at the server, to no effect.
> So, my question is - what could cause the CLR to not use available
> memory? There's something wrong with the server configuration, but I
> can't figure out what. Any help would be greatly appreciated!
> Below is the function in question.
> [SqlFunction(Name="_clrApplyStylesheet",
> DataAccess=DataAccessKind.Read)]
> public static SqlString _clrApplyStylesheet(SqlXml XmlData,
> SqlXml XmlStylesheet)
> {
> XPathDocument stylesheet, xmlData;
> XslCompiledTransform xTransform;
> System.Text.StringBuilder sBuilder;
> XmlWriter xWriter;
> stylesheet = new
> XPathDocument(XmlStylesheet.CreateReader());
> xmlData = new XPathDocument(XmlData.CreateReader());
>
> sBuilder = new System.Text.StringBuilder();
> xWriter = XmlWriter.Create(sBuilder);
> xTransform = new XslCompiledTransform();
> xTransform.Load(stylesheet);
> xTransform.Transform(xmlData, xWriter);
> return sBuilder.ToString();
> }
>|||Hi Steven,
Thank you so much - I increased memory allocation with the -g switch,
and it worked!
I wonder why it's such an obscure issue - your article (which I read
even before posting but wasn't sure if it was applicable) seems to be
almost the only one relevant to the issue.
So, when you do use the -g switch: can the Buffer Pool reclaim the
memory from "memtoleave" when it's not required, or are you actually
decreasing the memory available to Buffer Pool at all times? It's a
shared server, and I don't want to slow down everyone just so that my
code could work.
Abe