AppDomain marked for unload due to memory pressure
Hello everyone,
I have been having problems with our app domain being unloaded by SQL server 2005 with service pack 2. Although I have seen numerous forum postings about this, I had some more questions that I was really hoping someone would be able to answer for me:
1. Is there any way to easily determine what caused the app domain to unload?
It has only happened a very small number of times and cannot be easily reproduced. I don’t think it is a problem with the CLR code itself, but I would like to prove that this is the case the next time this happens if possible?
When it has happened we were not using the machine as a dedicated server for SQL. The same machine also runs an IIS 6.0 web server, and could well have had other programs like SQL Server Management Studio, and an IE client connected to the web server. I think once someone was running a virtual machine on the same machine, but is there a way to prove these external applications are causing the memory pressure?
If another external application is using all the available memory momentarily, will SQL server immediately unload the app domain if SQL server needs more memory? I believe when I once restarted the app domain up again after it has been unloaded it worked fine after that, but I guess the app domain itself could have been using all the memory… any thoughts?
2. Is there any way to stop the app domain being unloaded?
Ideally I would like to prevent the app domain being unloaded if possible, but if not is there some way that I can make this less likely. Some forums detailed using the ‘g’ parameter at start-up to give the CLR more memory, others mention setting the maximum server memory on SQL server.
We do not set the maximum server memory at all, but if most of the time everything works fine why would I want to limit the memory usage of SQL server. Also if it is an external application causing the memory pressure would this not happen regardless of this setting?
The machines in question only have 1GB of memory. Although increasing the amount of memory on the machines might help, I don’t want to do this without proving there is insufficient memory and knowing what is currently using up all the memory… I might have to try out some performance monitors, but as I can’t easily reproduce the problem it may never happen while I am profiling… anyone got any other thoughts?
Another thought, is would there be anyway to reserve memory that can only be used by SQL server or is this wishful thinking? We can probably limit all the other processes to have a maximum memory usage too, but this doesn’t really help unless the combined percentages are less than 100% and this just seems a really bad idea.
3. Running the CLR code as a separate service on the machine?
Our app domain acts as a gateway between our hardware units and the SQL database. We have a number of unsafe assemblies which create their own threads and run independently of any stored procedure calls.
One of the assemblies is responsible for socket handling and accepts incoming connections from the hardware units, receives messages from the units and queues them. This assembly is also used to create outgoing connections to the hardware units and will send messages to the units.
One of the assemblies is responsible for the database handling and extracts the required information from the messages, creates a new connection to the database and then updates the database as required. This assembly also polls the database for new messages, which will be forwarded on to the socket assembly to send these out to the hardware units.
In hindsight, it might have been better to just have a separate service, as I imagine the CLR was not designed for such assemblies… anyone have any thoughts on this? Has anyone else used multi-threading and sockets within the assemblies? Although, 99% of the time it all works perfectly we could really do with that 100%.
4. Detecting when the app domain has been unloaded?
Am I right in saying we do not know programmatically when the app domain is unloaded? What would happen to my threads that are running within the app domain when it is unloaded? I am thinking this problem could be a momentary memory usage issue and wondered whether there is a way to restart the app domain once it has been unloaded. I could have let SQL server agent periodically check to see if it is still there and if not recreate it, but I don’t really want to implement a solution for a problem I never want to happen.
Apologies for going on a bit, but I just have lot of questions as to what is going on. If anyone can help with any of the above questions it would be greatly appreciated… thanks!
Answers
The amount of memory on the SQL Server would be my first concern. 1GB of RAM just isn't very much. The OS alone can consume that relatively quickly, especially with IIS running the server as well. Hyperthreading isn't generally recommended with SQL Server however, it has nothing to do with your problem currently, which is all memory based. If anything having a true second core may make the Memory bottleneck that much more visible, as the processors may process faster than the memory can keep up and paging to disk begins to occur.
http://www.sqlmag.com/Article/ArticleID/96850/sql_server_96850.html
Based on the output of the memory script you ran, I would say that you have external pressure on memory that is likely causing the unloads. The only way to know is to monitor the memory performance counters at the OS Level until the problem occurs again.
All Replies
ForumFirstTimer wrote: Hello everyone,
I have been having problems with our app domain being unloaded by SQL server 2005 with service pack 2. Although I have seen numerous forum postings about this, I had some more questions that I was really hoping someone would be able to answer for me:
1. Is there any way to easily determine what caused the app domain to unload?
It has only happened a very small number of times and cannot be easily reproduced. I don’t think it is a problem with the CLR code itself, but I would like to prove that this is the case the next time this happens if possible?
When it has happened we were not using the machine as a dedicated server for SQL. The same machine also runs an IIS 6.0 web server, and could well have had other programs like SQL Server Management Studio, and an IE client connected to the web server. I think once someone was running a virtual machine on the same machine, but is there a way to prove these external applications are causing the memory pressure?
If another external application is using all the available memory momentarily, will SQL server immediately unload the app domain if SQL server needs more memory? I believe when I once restarted the app domain up again after it has been unloaded it worked fine after that, but I guess the app domain itself could have been using all the memory… any thoughts?
SQLCLR runs in a finite memory space in the SQLOS called the MemToLeave area or MTL. There are numerous factors that go into how large the MTL for your SQL Instance will be, and keep in mind that CLR is not the only thing that is running inside the MemToLeave area. OLE Automation, calling the system xp's, and other processes utilize this space. Run the query on this post:
http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3405947&SiteID=1
and see what your MTL memory size is set to. External demands on memory by the OS could definately cause Memory set trimming in SQL Server and an appdomain unload.
2. Is there any way to stop the app domain being unloaded?
Ideally I would like to prevent the app domain being unloaded if possible, but if not is there some way that I can make this less likely. Some forums detailed using the ‘g’ parameter at start-up to give the CLR more memory, others mention setting the maximum server memory on SQL server.
We do not set the maximum server memory at all, but if most of the time everything works fine why would I want to limit the memory usage of SQL server. Also if it is an external application causing the memory pressure would this not happen regardless of this setting?
The machines in question only have 1GB of memory. Although increasing the amount of memory on the machines might help, I don’t want to do this without proving there is insufficient memory and knowing what is currently using up all the memory… I might have to try out some performance monitors, but as I can’t easily reproduce the problem it may never happen while I am profiling… anyone got any other thoughts?
Another thought, is would there be anyway to reserve memory that can only be used by SQL server or is this wishful thinking? We can probably limit all the other processes to have a maximum memory usage too, but this doesn’t really help unless the combined percentages are less than 100% and this just seems a really bad idea.
Running SQL Server with 1GB is going to be the start of the issue. If you add applications and IIS on top of that, you have a huge memory bottleneck and you won't stop the app domain unloads from happening no matter what you do. In my experience on servers with 4GB+ memory, setting the max memory to 3GB to leave space for the OS works very good and if that doesn't completely solve the unload, setting the -g512 flag does. What edition of SQL Server is this?
3. Running the CLR code as a separate service on the machine?
Our app domain acts as a gateway between our hardware units and the SQL database. We have a number of unsafe assemblies which create their own threads and run independently of any stored procedure calls.
One of the assemblies is responsible for socket handling and accepts incoming connections from the hardware units, receives messages from the units and queues them. This assembly is also used to create outgoing connections to the hardware units and will send messages to the units.
One of the assemblies is responsible for the database handling and extracts the required information from the messages, creates a new connection to the database and then updates the database as required. This assembly also polls the database for new messages, which will be forwarded on to the socket assembly to send these out to the hardware units.
In hindsight, it might have been better to just have a separate service, as I imagine the CLR was not designed for such assemblies… anyone have any thoughts on this? Has anyone else used multi-threading and sockets within the assemblies? Although, 99% of the time it all works perfectly we could really do with that 100%.
Are all of these assemblies running in SQL Server CLR, or are the running outside of the database? Where are you getting the App Domain Unload messages, the SQL Server Error Log, the Windows Event Log, or Both?
4. Detecting when the app domain has been unloaded?
Am I right in saying we do not know programmatically when the app domain is unloaded? What would happen to my threads that are running within the app domain when it is unloaded? I am thinking this problem could be a momentary memory usage issue and wondered whether there is a way to restart the app domain once it has been unloaded. I could have let SQL server agent periodically check to see if it is still there and if not recreate it, but I don’t really want to implement a solution for a problem I never want to happen.
Apologies for going on a bit, but I just have lot of questions as to what is going on. If anyone can help with any of the above questions it would be greatly appreciated… thanks!
If it happens in SQL Servers Error Log, then you can poll the log, and generate an event. There really isn't enough information on everything you are using SQLCLR for to be certain, but I feel pretty safe in saying that your entire implementation is misplaced by being in SQL Server. SQLCLR exists to extend the TSQL procedural language, but it isn't there to run your application in the database server.
Hi Jonathan,
Thanks very much for the response, which was very useful and interesting to read some of your points. Also, when following your link I found a number of interesting articles that I hadn't seen before, so thanks very much for that!
In response to some of your questions...
Jonathan Kehayias wrote: Run the query on this post:
http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3405947&SiteID=1
and see what your MTL memory size is set to.
I run the query on one of the machines that has encountered the problem before and got the following results:
Total avail mem, KB: 297044
Max free size, KB: 136832
There seems to be enough memory available, but saying that everything has been working fine since it was last restarted. Is there anything else we can determine from this and would you expect to see something different if the same query was run after the AppDomain has been unloaded? Is it also worth monitoring this over time and what would this indicate if the memory available went down over time... it hasn't changed in the short time I have been monitoring it.
Jonathan Kehayias wrote: What edition of SQL Server is this?
The details of this instance of SQL server are as follows:
Product: Microsoft SQL Server Standard Edition
Operating System: NT 5.2 (3790)
Platform: NT INTEL x86
Version: 9.00.3068.00
Memory: 1022MB
Processors: 2
I actually expected us to be using Enterprise edition but apparently not. I also didn't expect to see 2 processors on this machine, and it turns out this particular machine has a single processor with hyper-threading enabled. I read somewhere about disabling hyper-threading, but would this be making it more likely to unload the AppDomain? I am not sure about the other machines where the problem has occurred, but will make sure I check this next time.
Jonathan Kehayias wrote: Are all of these assemblies running in SQL Server CLR, or are the running outside of the database? Where are you getting the App Domain Unload messages, the SQL Server Error Log, the Windows Event Log, or Both?
All the assemblies are running SQL Server, including a third party logging assembly that was available to us, which allows us to write output messages to a log file from any of the assemblies, so what we have is multiple assemblies, some running their own threads, and communicating with each other.... its not really just extending T-SQL.
The AppDomain unload messages are found in both the application log and SQL server logs. Does this indicate anything or rule out any possible causes?
As you say in your last statement, I am certain that out current implementation within SQL sever is misplaced, and I am going to recommend that this be removed into a separate Windows service. However, we will still need to support the current implementation so if you do have any thoughts, both generally or on any of the above information, then they would be greatly appreciated.
Thanks again and look forward to your response!
The amount of memory on the SQL Server would be my first concern. 1GB of RAM just isn't very much. The OS alone can consume that relatively quickly, especially with IIS running the server as well. Hyperthreading isn't generally recommended with SQL Server however, it has nothing to do with your problem currently, which is all memory based. If anything having a true second core may make the Memory bottleneck that much more visible, as the processors may process faster than the memory can keep up and paging to disk begins to occur.
http://www.sqlmag.com/Article/ArticleID/96850/sql_server_96850.html
Based on the output of the memory script you ran, I would say that you have external pressure on memory that is likely causing the unloads. The only way to know is to monitor the memory performance counters at the OS Level until the problem occurs again.
Hi Jonathan... thanks for all your help with this!
I think doing some performance monitoring on the SQL server machine is the way forward, which should hopefully give us a better idea of how much memory we need on the server, and it might also indicate what is causing the external memory pressure if it happens again. As a longer term goal I hope to use a separate service as opposed to the SQL CLR.
Will let you know how we get on and thanks again for all your help!
