SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


dm_os_process_memory shows more memory usage than dm_os_memory_clerks & dm_os_buffer_descriptors


dm_os_process_memory shows more memory usage than dm_os_memory_clerks & dm_os_buffer_descriptors

Author
Message
DennisPost
DennisPost
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2335 Visits: 617
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?
Sue_H
Sue_H
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77699 Visits: 15541
In the DMVs, you are looking at just portions of memory usage and also looking for memory change notifications with the ring buffer query.

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




DennisPost
DennisPost
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2335 Visits: 617
Thanks 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.

Sue_H
Sue_H
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77699 Visits: 15541
DennisPost - Monday, December 11, 2017 12:23 AM
Thanks 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



DennisPost
DennisPost
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2335 Visits: 617
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.
DennisPost
DennisPost
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2335 Visits: 617
We had to restart the SQL service.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search