Appdomain Memory errors in SQL Server Errorlog
-
Hi Experts
Need help and guidance on an on-going memory issue on an SQL 2014 SSIS server. I am not sure how to isolate things here or where to start to avoid below memory warnings in the errorlog.
Brief Problem description :
========================
We have an Datawarehouse environment wherin a lot of ssis packages run every 15 mins as a part of SQL Agent jobs which are scheduled every 15 mins/4hours depending on source system availability.
Having said that, I am seeing some memory related messages from SQL Server ERRORLOG. More importantly, I would like to know why am I seeing appdomain errors on 64-bit servers.
Based on my knowledge, usually we used to see appdomain errors on 32 bit systems when MTL (mem-to-leave) portion is less.
Could anybody please explain why we are seeing these errors and how can I avoid or minimise such errors.
Enviroment
===========
SQL Server details
====================
Microsoft SQL Server 2014 (SP1-CU5) (KB3130926) - 12.0.4439.1 (X64) Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
OS information and Memory information
========================================
OS Name Microsoft Windows Server 2012 R2 Datacenter
Version 6.3.9600 Build 9600
System Manufacturer Microsoft Corporation
System Model Virtual Machine
System Type x64-based PC
Processor Intel(R) Xeon(R) CPU E5-4650L 0 @ 2.60GHz, 2600 Mhz, 8 Core(s), 8 Logical Processor(s)
Time Zone Pacific Daylight Time
Installed Physical Memory (RAM) 56.0 GB
Total Physical Memory 56.0 GB
Available Physical Memory 21.1 GB
Total Virtual Memory 75.9 GB
Available Virtual Memory 38.2 GB
Page File Space 19.9 GB
From Errorlog ( i see a lot of below errors getting repeated every now and then)
========================
2016-09-19 07:13:40.79 spid9s AppDomain 186 (master.sys[runtime].418) is marked for unload due to memory pressure.
2016-09-19 07:13:40.79 spid9s AppDomain 185 (mssqlsystemresource.dbo[runtime].417) is marked for unload due to memory pressure.
2016-09-19 07:13:40.79 spid36s AppDomain 186 (master.sys[runtime].418) unloaded.
2016-09-19 07:13:40.79 spid9s AppDomain 184 (SSISDB.dbo[runtime].416) is marked for unload due to memory pressure.
2016-09-19 07:13:40.79 spid9s AppDomain 185 (mssqlsystemresource.dbo[runtime].417) unloaded.
2016-09-19 07:13:40.79 spid9s AppDomain 184 (SSISDB.dbo[runtime].416) unloaded.
2016-09-19 07:14:18.92 spid92 AppDomain 187 (SSISDB.dbo[runtime].419) created.
2016-09-19 07:14:19.02 spid92 Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=12.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil' loaded into appdomain 187 (SSISDB.dbo[runtime].419).
select * from sys.assemblies
name principal_id assembly_id clr_name permission_set permission_set_desc is_visible create_date modify_date is_user_defined
Microsoft.SqlServer.Types 4 1 microsoft.sqlserver.types, version=12.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil 3 UNSAFE_ACCESS 1 2012-02-10 20:16:00.850 2012-02-10 20:16:01.437 0
select * from sys.dm_clr_appdomains
appdomain_address appdomain_id appdomain_name creation_time db_id user_id state strong_refcount weak_refcount cost value compatibility_level total_processor_time_ms total_allocated_memory_kb survived_memory_kb
0x00000002EC1E0200 196 SSISDB.dbo[runtime].429 2016-09-21 23:31:36.770 6 1 E_APPDOMAIN_SHARED 1 5 406918 7109194 110 2875 2125677 1118
0x0000001877BDC200 113 master.sys[runtime].233 2016-08-26 12:44:34.450 1 4 E_APPDOMAIN_SHARED 48 1 8192 168820727 120 0 643 362
select * from sys.dm_clr_loaded_assemblies
assembly_id appdomain_address load_time
65537 0x00000002EC1E0200 2016-09-21 23:31:37.183
-- perfmon counter values
counter_name Mem_GB
Target Server Memory (KB) 23GB
Total Server Memory (KB) 23GB
- highest memory consumers
-- Memory Clerk Usage for instance (Query 43) (Memory Clerk Usage)
-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)
SELECT TOP(10) mc.[type] AS [Memory Clerk Type],
CAST((SUM(mc.pages_kb)/(1024.0 *1024)) AS DECIMAL (15,2)) AS [Memory Usage (GB)],
CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type]
ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);
go
Memory Clerk Type Memory Usage (GB) Memory Usage (MB)
MEMORYCLERK_SQLBUFFERPOOL 17.32 17738.16
MEMORYCLERK_SQLQERESERVATIONS 5.43 5555.73
USERSTORE_DBMETADATA 1.51 1550.21
CACHESTORE_SQLCP 1.40 1435.45
OBJECTSTORE_LOCK_MANAGER 0.41 423.16
MEMORYCLERK_SQLSTORENG 0.17 175.15
MEMORYCLERK_SOSNODE 0.05 55.57
MEMORYCLERK_SQLCLR 0.03 33.36
CACHESTORE_OBJCP 0.03 31.02
USERSTORE_SCHEMAMGR 0.02 17.00
--Buffer pool distribution
-- Per database DataCache usage inside buffer pool
SELECT
CASE database_id WHEN 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END as "DatabaseName",
COUNT(*) PageCount,
CAST(COUNT(*) * 8 / 1024.0 AS NUMERIC(10, 2)) as "Size (MB)-Only DataCache-in-BufferPool"
From sys.dm_os_buffer_descriptors
--WHERE database_id = DB_ID('DUMMY')
GROUP BY db_name(database_id),database_id
ORDER BY "Size (MB)-Only DataCache-in-BufferPool" DESC OPTION (RECOMPILE);
go
DatabaseName PageCount Size (MB)-Only DataCache-in-BufferPool
SSISDB 2004259 15658.27
Alerting 155376 1213.88
tempdb 52595 410.90
demoConfig 34753 271.51
msdb 20619 161.09
ResourceDB 2338 18.27
master 370 2.89
db1 242 1.89
model 97 0.76
db3 32 0.25
-- See external dll's or modules getting loaded into sql server address space
select base_address,convert(varchar(20),file_version) file_version,convert(varchar(20),product_version) product_version,convert(varchar(30),company) company,convert(varchar(42),[description]) [description],name
from sys.dm_os_loaded_modules
where company not like '%Microsoft%'
--no output
SELECT name, value_in_use
FROM sys.configurations WITH (NOLOCK)
where name in ('max server memory (MB)','min server memory (MB)','optimize for ad hoc workloads','xp_cmdshell','priority boost','lightweight pooling','clr enabled','affinity I/O mask','affinity64 I/O mask','affinity mask','affinity64 mask','max degree of parallelism','cost threshold for parallelism','fill factor (%)','network packet size (B)')
ORDER BY name OPTION (RECOMPILE);
name value_in_use
affinity I/O mask 0
affinity mask 0
affinity64 I/O mask 0
affinity64 mask 0
clr enabled 1
cost threshold for parallelism 5
fill factor (%) 0
lightweight pooling 0
max degree of parallelism 1
max server memory (MB) 24000
min server memory (MB) 16
network packet size (B) 4096
optimize for ad hoc workloads 0
priority boost 0
xp_cmdshell 0
Thanks,
Sam
-
Quick questions
1) How large are the data sets which are being processed in the SSIS packages?
2) Are those sets sorted within the packages?
3) Are the DefaultMaxBufferSize and DefaultMaxBufferRows values set to the defaults?
-
my comments
1) How large are the data sets which are being processed in the SSIS packages?
The sets are quite large. 2-3 millions rows.
2) Are those sets sorted within the packages?
Yes. few data sets are sorted.
3) Are the DefaultMaxBufferSize and DefaultMaxBufferRows values set to the defaults?
Yes.
-
Hi Experts,
Any thoughts??
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply