Troubleshooting SQL CLR memory pressure

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