Troubleshooting SQL CLR memory pressure

02 October,2017 by Jack Vamvas

Question: I can see error messages in the SQL Server Error Log such as “AppDomain xxxxxxxxxxxxx is marked for unload due to memory pressure”.

How can I troubleshoot the memory pressure?

Answer: This message can be challenging to identify the source. I don’t define this message as an error message. It’s more of an informational message that reports on server memory pressure. Because there was memory pressure , the server unloaded app domain to free server memory. The app domain is unloaded causing the associated memory to be freed.

It is common for this information message to appear when CLR is involved. For example , the CLR may be creating very large arrays , the arrays requiring more memory than currently available on the server.

It’s worth mentioning that MAX SERVER MEMORY setting limits the Buffer Pool. It doesn’t limit other SQL Server activity – such as CLR which can access remaining server Non Buffer Pool memory is used by CLR procedures. Non Buffer Pool MCLR procedures can include User Defined Functions (UDF) , stored procedures, user defined data types, and user defined aggregates.  

The most effective methods can be found on a previous post - http://www.sqlserver-dba.com/2013/01/sql-server-appdomain-is-marked-for-unload-due-to-memory-pressure.html

Along with the suggestions made on the link above is a useful query, which include time consumed within .NET Framework CLR

 

SELECT 
(SELECT text FROM sys.dm_exec_sql_text(qs.sql_handle)) AS query_text, qs.*
FROM sys.dm_exec_query_stats AS qs
WHERE qs.total_clr_time > 0 
 ORDER BY qs.total_clr_time desc

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Troubleshooting SQL CLR memory pressure


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer