SQL Server 2005 .NET Framework execution was aborted by escalation policy because of out of memory on 32 bit system
The above error was returned on a SQL Server 32 bit system that is used for an inventory application, the initial though on out of memory was to see what is the resource usage at that moment as the SQL Server memory settings are set to DYNAMIC and all necessary AWE settings are enabled.
As you may be aware that a 32-bit SQL Server, the ONLY thing that can use memory above the 4GB line is the buffer cache. Say if you have the /3GB switch set, then SQL Server with any other applications on the server has 3 GB memory, otherwise it only has 2 GB memory with the rest of the sub-4GB memory going to the OS. THe usual reference on the web including Microsoft documentation refers that to move to 64 bit system, which is impossible at this point of time as the Management are not ready to spend budge on increasing the Operating System & SQL system status for this application.
Also I saw few forums posts there reference that such .NET assembly (through SQLCLR) to load data files was failing on 32-bit SQL Server and no issues report on a 64-bit based systems.
To my knowledge it is evident that the SQL Server might suffer with memory issues when a dodgy code is used or any CLR function are used when the memory settings are not proper. It is also hard to diagnose resource leaks in managed code and when such managed code used by sql server would be even harder. No easy way to find out such memory and leaks impossible to test anything on production server when you have no clue that whether it will fetch any good result. For further resolution on the issue it is better to findout reason for unresponsive SQL Server that can be caused by a multitude of factors.
Over here not many cases I have had chance to use Dedicated Admin Console DAC which is a great way to determine the relevant factors and take the appropiate action based on facts, not on speculation. In this case I was able to find the information on CLR procedures that blocked the network I/O by using DMVs sys.dm_os_workers and sys.dm_os_schedulers which has resulted in a large number of workers blocked and schedulers showing large number of tasks queued up). Further I have used the DMVs sys.dm_os_memory_clerks to identify the CPU consumption at this point with a further lookup on the current tasks execution by running sys.dm_os_tasks DMV.
On the resolution aspect of the above problem I have found that everytime the application uses the function that loads 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 memory fragmentation. Also I have found the reference on Technet that :
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
Whereby I have tested this fully due to the constraints on the project delivery at this point, this has been leftover to the Developers to consider any code changes that are required and test the same thorougly to identify whether its a feasible solution.
Out of this the only lesson I can see is to resolve the issue by upgrading the system to use 64 bit architecture to take benefit of large memory support or change the code in order to reduce the memory consumption unnecessarily.