Loading data files using SQLCLR assembly causes Out Of Memory Errors on 32-bit SQL Server

  • Article

Recently got one where using .NET assembly (through SQLCLR) to load data files was failing on 32-bit SQL Server. This was working fine on 64bit SQL Server but it was failing on 32 bit SQL Server with:
<error>
2008-04-09 14:37:00.87 spidxxx Error: 6532, Severity: 16, State: 49.
2008-04-09 14:37:00.87 spidxxx .NET Framework execution was aborted by escalation policy because of out of memory.
System.Threading.ThreadAbortException: Thread was being aborted.
System.Threading.ThreadAbortException:
at System.IO.File.ReadAllBytes(String path)
</eror>

Resolution:

  • Found that here its actually loading the data files using System.IO.File.ReadAllBytes. This was causing the entire file to get loaded in MTL and used to eat a lot MTL space.
  • Also everytime we load a CLR assembly, SQL has to load not just that assembly but the .NET engine (mscorwks) and all the assembly it references. On a 32-bit machine, this becomes an overload quickly and causes MTL fragmentation.

Found that using loading the files using System.IO.File class is not a good idea. Rather, we should use Filestreams.
  ex. FileStream fstr = new FileStream(path,FileMode.Open,FileAccess.Read);

Another way to ensure that the memory utilized by the assembly is freed as soon as the function exits:
  > We need to set the assembly permission level to unsafe
  > At the end of the function, before the return statement, call: GC.Collect();
  > This will cause the GC to kick in and reclaim the space
But this has the disadvantage that we have to reduce the assembly permission level. Many situations it may not be feasible.

Best way to resolve this is to use 64 bit architecture to take benefit of large memory support.