December 8, 2017 at 5:44 am
Hi,
I'm looking for some help finding some missing memory and how to reclaim it without restarting the SQL service.
The original issue:
A SSIS package is hanging on a sort operation. Only ~860K rows, ~45 bytes per row = ~37 MB.
This is the 3rd time this is happened.
Windows 2016 resource monitor is showing that SQL (2016 SP1) is using 40 GB from the installed 48 GB RAM, with only ~550 MB free and ~1200 MB standby.
Upon investigating this I noticed that I couldn't account for ~9 GB RAM.
Btw, we are not experiencing any memory pressure. PLE ~2700, stolen memory = ~4000 MB
Current memory related values:EXEC sp_Configure 'min server memory (MB)' -- run_value = 8000
EXEC sp_Configure 'max server memory (MB)'-- run_value = 28000
Digging into the SQL DMVs shows a difference between the memory usage values. ~9 GB more than the "max server memory (MB)" value.SELECT Memory_Usage_MB = physical_memory_in_use_kb / 1024
FROM sys.dm_os_process_memory
/*
Memory_Usage_MB
37644
*/SELECT MemoryMB = SUM(MC.pages_kb) / 1024,
VirtualMBRes = SUM(MC.virtual_memory_reserved_kb) / 1024,
VirtualMBComm = SUM(MC.virtual_memory_committed_kb) / 1024
FROM sys.dm_os_memory_clerks MC
/*
MemoryMB VirtualMBRes VirtualMBComm
26337 25521 1559
*/SELECT MBUsed = COUNT (1) / 128,
MBEmpty = SUM(CAST(free_space_in_bytes AS BIGINT)) / (1024 * 1024)
FROM sys.dm_os_buffer_descriptors
/*
MBUsed MBEmpty
23544 4101
*/DECLARE @DateVal Datetime = DATEADD(SECOND, -60, GETDATE())
;WITH RingBuffer
AS
(
SELECT CAST(dorb.record AS XML) AS xRecord,
dorb.timestamp
FROM sys.dm_os_ring_buffers AS dorb
WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
)
SELECT LowMemoryCnt = COUNT(1)
FROM RingBuffer AS rb
CROSS APPLY rb.xRecord.nodes('Record') record (xr)
CROSS JOIN sys.dm_os_sys_info AS dosi
WHERE xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') IN ('RESOURCE_MEMPHYSICAL_LOW', 'RESOURCE_MEMVIRTUAL_LOW')
AND DATEADD(SECOND, -1 * (dosi.ms_ticks / 1000 - (rb.timestamp / 1000)), GETDATE()) >= @DateVal
/*
LowMemoryCnt
0
*/
I'm not sure what to look for in DBCC MEMORYSTATUS
Any idea's?
December 8, 2017 at 7:48 am
Max server memory doesn't fully control or account for all of the memory used by an instance. Max memory controls buffer pool, caches, lock manager, compile memory and CLR memory. These are tracked by the different memory clerks.
Memory for thread stacks, memory heaps, non-SQL Server linked server providers and other non-SQL Server dlls are not part of the max server memory.
It not at all unusual to see the totally memory usage by a SQL Server instance be higher than the max memory setting.
Sue
December 11, 2017 at 12:23 am
Memory for thread stacks, memory heaps, non-SQL Server linked server providers and other non-SQL Server dlls are not part of the max server memory.
How can I see more information about these memory users?
December 11, 2017 at 8:00 am
DennisPost - Monday, December 11, 2017 12:23 AMThanks for your input Sue.Memory for thread stacks, memory heaps, non-SQL Server linked server providers and other non-SQL Server dlls are not part of the max server memory.
How can I see more information about these memory users?
When you talk about "non-SQL Server dlls" I think about CLRs which have their own memory clerk. Although we do have some linked servers, they are only used for occasional administrative tasks.
Things such as third party products, user defined extended stored procedures can also use DLLs, it's not just CLRs.
You can find several different things to check the memory usage in this post:
SQL Server 2012 memory consumption outside the buffer pool
Sue
December 14, 2017 at 5:33 am
Thanks Sue.
That link lead me to more troubleshooting tips.
Unfortunately, we still haven't found the culprit(s).
Here are some of the links we have come across.
How to measure the SQL Server MTL Memory
Troubleshooting memory leaks
There is no “MemToLeave” for the 64bit version of the SQL Server Engine!
MTL Consumers
This last link provided some new insight, but again no solution was found. Here are the results.
A : Although there are 3 linked servers, Only one is used and only monthly for a DBA task.
B : Our XML serializing and deserializing are handle by in-house windows services outside of SQL.
Ci : No mention of 3rd party extended SPs in the sql error logs. No occurrences of "dll" or "XSP" in the error logs.
Cii : Ole Automation Procedures is disabled in sys.configuration. There are no calls to sp_OA stored procedures in sys.dm_exec_procs_stats.
D : There are instances of multi page query plans, but they only add up to ~620 MB.
E : The CLR memory clerk is only using 30 MB.
F : We do not use MAXTRANSFERSIZE when backing up.
G : There were connections that use a TCP packet size larger than 8192 KB. These were all SSIS connection managers and have been reset to 0.
Strangely the SQL server is still running smoothly. There are no memory related errors. The only symptoms are from the original issue of a SORT operator in SSIS hanging and another SSIS package that has crashed a couple of times out of its 1000+ executions.
Any new suggestions are welcome.
December 27, 2017 at 5:17 am
We had to restart the SQL service.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply