October 22, 2015 at 7:36 am
Hi guys,
I'm supporting 4 SQL instances running on 4 servers. Each are running SQL Server 2012 Enterprise 64bit SP 1. 41 of the databases sat on all of these instances are running under 3 Availability Groups (1x19, 1x18 and 1x4).
Instance 1 is Primary to the first and third group. It is a readable secondary using synchronous availability with auto failover for the second group.
Instance 2 is Primary to the second group. It is a readable secondary using synchronous availability with auto failover for the first and third groups.
Instance 3 is a readable secondary using asynchronous availability with manual failover for all 3 groups.
Instance 4 is a non-readable secondary using asynchronous availability with manual failover for all 3 groups.
All 4 instances are currently showing high amounts of stolen memory which appear to be down to the MEMORYCLERK_SQLSTORENG memory clerk and the MEMOBJ_PERDATABASE memory object. Has anyone seen this before? If so, does it relate to Availability Groups or is it another issue, and is there anything I can do to reduce the amount of stolen memory?
Also, stolen memory is currently around 75GB on instance 1, but I can only see around 49GB of this being used by memory clerks \ memory objects. Is the missing 26GB down to a memory leak or am I missing something? Here are some stats I've collected on Instance 1. The max memory setting is 168GB.
select counter_name, cntr_value / 1024.0 as counter_value from sys.dm_os_performance_counters where object_name in (N'SQLServer:Memory Manager') and counter_name in (N'Database Cache Memory (KB)', N'Stolen Server Memory (KB)')
counter_name counter_value
Database Cache Memory (KB) 92045.140625
Stolen Server Memory (KB 76341.179687
select sum(pages_kb) / 1024 as SQLStoreng from sys.dm_os_memory_clerks where type = N'MEMORYCLERK_SQLSTORENG'
SQLStoreng
34734
select sum(pages_in_bytes) / 1024 / 1024 as PerDatabase from sys.dm_os_memory_objects where type = N'MEMOBJ_PERDATABASE'
PerDatabase
34661
select sum(pages_kb) / 1024 as OtherClerks from sys.dm_os_memory_clerks where type not in (N'MEMORYCLERK_SQLBUFFERPOOL', N'MEMORYCLERK_SQLSTORENG')
OtherClerks
15282
select sum(pages_in_bytes) / 1024 / 1024 as OtherObjects from sys.dm_os_memory_objects where type <> N'MEMOBJ_PERDATABASE'
OtherObjects
15804
Thanks,
Rob
October 22, 2015 at 7:36 am
A shortened version of DBCC MEMORYSTATUS
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 8478769152
Available Virtual Memory 5600995225600
Available Paging File 58604822528
Working Set 16386609152
Percent of Committed Memory in WS 100
Page Faults 634984489
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
Memory Manager KB
---------------------------------------- --------------------
VM Reserved 3118270716
VM Committed 10701512
Locked Pages Allocated 165459356
Large Pages Allocated 1128448
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 176160768
Current Committed 176160872
Pages Allocated 148485104
Pages Reserved 11464
Pages Free 804376
Pages In Use 78192920
Page Alloc Potential 89159816
NUMA Growth Phase 2
Last OOM Factor 0
Last OS Error 0
Memory node Id = 0 KB
---------------------------------------- --------------------
VM Reserved 3118230588
VM Committed 10661408
Locked Pages Allocated 77418968
Pages Allocated 66285968
Pages Free 40112
Target Committed 88080376
Current Committed 88080376
Foreign Committed 3222068
Away Committed 0
Taken Away Committed 0
Memory node Id = 1 KB
---------------------------------------- -----------
VM Reserved 40064
VM Committed 40084
Locked Pages Allocated 88040388
Pages Allocated 82199184
Pages Free 764224
Target Committed 88080376
Current Committed 88080472
Foreign Committed 1228
Away Committed 0
Taken Away Committed 0
Memory node Id = 64 KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 20
Locked Pages Allocated 0
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 91037612
VM Committed 524288
Locked Pages Allocated 6761060
SM Reserved 0
SM Committed 0
Pages Allocated 15976992
MEMORYCLERK_SQLBUFFERPOOL (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 81199064
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------- -----------
VM Reserved 91037612
VM Committed 524288
Locked Pages Allocated 6761060
SM Reserved 0
SM Committed 0
Pages Allocated 97176056
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- -----------
VM Reserved 113216
VM Committed 0
Locked Pages Allocated 113216
SM Reserved 0
SM Committed 0
Pages Allocated 152048
MEMORYCLERK_SQLSTORENG (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 35417168
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------- -----------
VM Reserved 113216
VM Committed 0
Locked Pages Allocated 113216
SM Reserved 0
SM Committed 0
Pages Allocated 35569216
Buffer Pool Value
---------------------------------------- -----------
Database 12147046
Simulated 2533081
Target 458391552
Dirty 1267481
In IO 0
Latched 31
Page Life Expectancy 1329
Procedure Cache Value
---------------------------------------- -----------
TotalProcs 73918
TotalPages 840147
InUsePages 1221
Global Memory Objects Pages
---------------------------------------- -----------
Resource 4345
Locks 507359
XDES 2476
DirtyPageTracking 49
SETLS 32
SubpDesc Allocators 32
SE SchemaManager 20713
SE Column Metadata Cache 57837
SE Column Metadata Cache Store 54
SQLCache 6250
Replication 2
ServerGlobal 111
XP Global 2
SortTables 3
Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 7470315
Current Max 7470315
Future Max 7470315
Physical Max 16650240
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Small Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 122880
Current Max 122880
Future Max 122880
Remote Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 8325120
Current Max 8325120
Query Memory Objects (default) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 11372715
Current Max 11372715
Future Max 11372715
Physical Max 16650240
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Small Query Memory Objects (default) Value
---------------------------------------- -----------
Grants 2
Waiting 0
Available 122479
Current Max 122880
Future Max 122880
Remote Query Memory Objects (default) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 8325120
Current Max 8325120
Optimization Queue (internal) Value
---------------------------------------- --------------------
Overall Memory 146565758976
Target Memory 102846889984
Last Notification 1
Timeout 6
Early Termination Factor 5
Small Gateway (internal) Value
---------------------------------------- -----------
Configured Units 128
Available Units 128
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
Medium Gateway (internal) Value
---------------------------------------- -----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
Big Gateway (internal) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
Optimization Queue (default) Value
---------------------------------------- --------------------
Overall Memory 146565758976
Target Memory 68460101632
Last Notification 1
Timeout 6
Early Termination Factor 5
Small Gateway (default) Value
---------------------------------------- -----------
Configured Units 128
Available Units 128
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
Medium Gateway (default) Value
---------------------------------------- -----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
Big Gateway (default) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
Memory Pool Manager Pages
---------------------------------------- -----------
Reserved Current 401
Reserved Limit 12262763
Memory Pool (internal) Pages
---------------------------------------- -----------
Allocations 7948507
Predicted 7948510
Private Target 0
Private Limit 0
Total Target 22020097
Total Limit 22020097
OOM Count 0
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- -----------
Allocations 134439
Rate 3
Target Allocations 8233850
Future Allocations 0
Overall 17891328
Last Notification 1
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- -----------
Allocations 4455144
Rate -10
Target Allocations 12554552
Future Allocations 0
Overall 17891328
Last Notification 1
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- -----------
Allocations 0
Rate 0
Target Allocations 8099408
Future Allocations 0
Overall 17891328
Last Notification 1
MEMORYBROKER_FOR_COMMITTED (internal) Pages
---------------------------------------- -----------
Allocations 3358922
Rate 0
Target Allocations 11458330
Future Allocations 0
Overall 17891328
Last Notification 1
MEMORYBROKER_FOR_XTP (internal) Pages
---------------------------------------- -----------
Allocations 2
Rate 0
Target Allocations 8099410
Future Allocations 0
Overall 17891328
Last Notification 1
Memory Pool (default) Pages
---------------------------------------- -----------
Allocations 1810652
Predicted 5972179
Private Target 0
Private Limit 0
Total Target 22020097
Total Limit 22020097
OOM Count 0
MEMORYBROKER_FOR_CACHE (default) Pages
---------------------------------------- -----------
Allocations 1552081
Rate -389
Target Allocations 9651489
Future Allocations 0
Overall 17891328
Last Notification 1
MEMORYBROKER_FOR_STEAL (default) Pages
---------------------------------------- -----------
Allocations 256746
Rate 792
Target Allocations 8356946
Future Allocations 0
Overall 17891328
Last Notification 1
MEMORYBROKER_FOR_RESERVE (default) Pages
---------------------------------------- -----------
Allocations 1825
Rate -7537
Target Allocations 12261968
Future Allocations 4162560
Overall 17891328
Last Notification 1
MEMORYBROKER_FOR_XTP (default) Pages
---------------------------------------- -----------
Allocations 0
Rate 0
Target Allocations 8099408
Future Allocations 0
Overall 17891328
Last Notification 1
Memory Broker Clerk (Buffer Pool) Pages
---------------------------------------- ----------------------
Total 12147049
Simulated 2533081
Simulation Benefit 5.52695138888341E-10
Internal Benefit 0
External Benefit 0
Value Of Memory 0
Periodic Freed 0
Internal Freed 0
November 11, 2015 at 8:03 am
Having looked at several servers it appears, as per the name of the memory object "MEMOBJ_PERDATABASE", there is nearly one of these memory objects per database.
The issue is 3 of the SQL instances are showing over 35GB for one database, and one of the secondaries is showing 22GB for another database. All other databases are 50MB or less and this seems to be the case for every database on the remaining servers we administer.
So the questions are:
What does this memory object store?
How can you find out which database relates to a given MEMOBJ_PERDATABASE object?
Why have specific ones grown so big?
March 29, 2016 at 9:34 am
The issue was found to be a bug in SQL Server 2012 and will also exist in 2014.
The issue is that HADR AG secondary databases do not delete change tracking committed data from memory (i.e. the data in memory that is reported by sys.dm_tran_commit_table).
Microsoft are currently writing a fix which will be in a future cumulative update for SP2 (more than likely CU12).
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply