April 26, 2010 at 2:22 pm
I have "AppDomain is marked for unload due to memory pressure" errors in the SQL log file. The spikes of available mamory on the server when this happens go as low as 10 MB. This happens when we have another process (TSM - backup to tape) runs on the machine. We checked that TSM does not require much - just a few MB of memory to run, still we are getting memory pressure problem. There is nothing else running on this server.
The server has 16GB memory. SQL Server (2005 Entreprise SP3 64 bit) max and min memory settings are 12GB. It keeps this memory and does not release it during memory pressure. (before I had min memory setting set to 4 GB, and SQL Server released all memory above 4 GB).
We have SQL CLR (managed code) running. Here are some memory counters: from dm_os_memory_clerks:
type single_pages_kb multi_pages_kb virtual_memory_reserved_kb virt_mem_commit_kb
MEMORYCLERK_SQLGENERAL 3528 4000 0 0
MEMORYCLERK_SQLCLR 116010488 6309376 18548
MEMORYCLERK_SQLBUFFERPOOL 0 1168 16809984 16384
MEMORYCLERK_SQLOPTIMIZER 3528 96 0 0
CACHESTORE_OBJCP 7584 80 0 0
CACHESTORE_SQLCP 1208 0 0 0
I am not sure what to look for. Would be the problem TSM, SQL CLR or some settings in SQl Server?
Please help!
April 27, 2010 at 6:45 am
When you get down to 10MB of available memory, what is using it? If SQL Server is only using 12GB, TSM a few MB, where are the other 3.x GB?
Can you look at task manager to see what else is using memory?
April 27, 2010 at 8:36 am
I think Windows Task manager (in Processes) shows only the normal memory, but not the "upper" memory. For instance in Task Manager I see that SQL Server uses only 112 MB, but I know it uses 12 GB.
My problem is that when the available memory is down to 10Mb I don't know how to look for what is using it.
April 27, 2010 at 2:02 pm
Track SQL server memory using below mentioned code.
SELECT OBJECT_NAME
,Counter_Name
,( cntr_value / 1024 ) AS [Counter Value (MB)]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE 'SQLServer:Memory Manager%'
AND counter_name LIKE 'Target Server Memory (KB)%'
Download http://www.sysinternals.com/Utilities/ProcessExplorer.html and add the Private Bytes column to the process view, that should show that memory allocated by the application that can not be shared with other applications (hence the naming "private").
April 28, 2010 at 8:29 pm
Hi,
Here is an article that has a lot of information on problem http://support.microsoft.com/kb/969962 (although it describes issue for 32bit SQL Server). We had similar issue on SQL Server 2005 SP3 after application upgrade ("application domain unload" messages), but did not have memory issues.
In my case I had to work with developers to verify that CLR code developed using the guidelines from the article above:
"When you develop custom SQL CLR applications, use SQL CLR memory carefully. Use the following guidelines when designing SQL CLR applications.
oAvoid caching large amount of data using objects such as DataTables. If you need to access SQL Server data, you use TSQL to retrieve just the data you need in your application.
oAvoid using static variables in your code to store large objects.
oCreate objects as late, and release them as early, as possible. "
Something else to check... On 64bit SQL Server make sure that SQL Server service account has "lock pages in memory" permissions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply