9

I've got an instance of SQL Server 2012 SP2 Enterprise Edition consuming ~20GB of memory higher than the max. memory limit. The instance is limited to 65GB but the physical memory in use from the below query shows 86GB

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO

The server is physical with 2 NUMA nodes. Is there a way that can I find out what is consuming the memory outside of the buffer pool (I'm assuming that is what's happening)?

Here's the output of DBCC MEMORYSTATUS:-

output of DBCC MEMORYSTATUS

And here'e the set memory limit:-

screen shot of memory limit

Thanks in advance.

UPDATE:- I've run the query that Aaron suggested

SELECT TOP (20) * FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC

Here's the output:-

MemoryClerkOutput

The SUM of pages_kb comes to ~60GB

UPDATE 2:- Full output of DBCC MEMORYSTATUS is here:- http://pastebin.com/nGn6kXEc

UPDATE 3:- Output of Shanky's scripts in excel file here:- http://jmp.sh/LKRlH4K

UPDATE 4:- Screenshot of the output of:-

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO

PhysMemInUse Screenshot

So this seems to indicate that SQL Server is using more than the 65GB set.

  • What does this yield? SELECT TOP (20) * FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC;? – Aaron Bertrand Jan 21 '15 at 17:15
  • Hi Aaron, thanks for replying. I'll update the question with the output now – dbafromthecold Jan 21 '15 at 17:18
10

Max server memory controls buffer pool and all page size allocations, but still does not control things like direct Windows allocations (linked servers, sp_OA, XPs), memory required for threads/thread stacks, etc.

You can probably expect this to be higher on NUMA (though I'm not sure 20 GB is normal); the point is, you can't expect max server memory to fully control the memory used by an instance of SQL Server. If you want the whole instance (not just buffer pool, plan caches, and CLR) to use no more than 64GB, you should set max server memory to something lower.

Some potential ideas for tracking this down (I will normalize everything to MB):

  • performance counters

    See if anything jumps out here as excessively large:

    SELECT counter_name, instance_name, mb = cntr_value/1024.0
      FROM sys.dm_os_performance_counters 
      WHERE (counter_name = N'Cursor memory usage' and instance_name <> N'_Total')
      OR (instance_name = N'' AND counter_name IN 
           (N'Connection Memory (KB)', N'Granted Workspace Memory (KB)', 
            N'Lock Memory (KB)', N'Optimizer Memory (KB)', N'Stolen Server Memory (KB)', 
            N'Log Pool Memory (KB)', N'Free Memory (KB)')
      ) ORDER BY mb DESC;
    
  • top 20 clerks

    You've already done this, but for completeness:

    SELECT TOP (21) [type] = COALESCE([type],'Total'), 
      mb = SUM(pages_kb/1024.0)
    FROM sys.dm_os_memory_clerks
    GROUP BY GROUPING SETS((type),())
    ORDER BY mb DESC;
    
  • thread stack size

    First, make sure this is zero, and not some custom number (if it is not 0, find out why, and fix it):

    SELECT value_in_use
      FROM sys.configurations 
      WHERE name = N'max worker threads';
    

    But you can also see how much memory is being taken up by thread stacks using:

    SELECT stack_size_in_bytes/1024.0/1024 
      FROM sys.dm_os_sys_info;
    
  • 3rd party modules loaded

    SELECT base_address, description, name
      FROM sys.dm_os_loaded_modules 
      WHERE company NOT LIKE N'Microsoft%';
    
    -- you can probably trace down memory usage using the base_address
    
  • memory-related DMVs

    You may also be able to spot something out of the ordinary looking at these DMVs:

    SELECT * FROM sys.dm_os_sys_memory;
    SELECT * FROM sys.dm_os_memory_nodes WHERE memory_node_id <> 64;
    

This article was written before SQL Server 2012, so some column names and calculations may have to be adjusted, but may give some other avenues to try as well:

Some good background in another article on that site too:

Some good info about the types of things that use memory outside of max server memory (but no good data about how to collect the actual usage):

  • Thanks Aaron, the server has plenty of memory available I just wanted to see if I could find out what was using that 20GB. Is there a way of identify memory consumption of Direct Windows Allocations or Thread Stacks? – dbafromthecold Jan 21 '15 at 17:56
  • I've the run scripts and the Stolen Server Memory (KB) counter is at 14GB. Going to dig to see if I can get any further information – dbafromthecold Jan 21 '15 at 20:53
  • Stolen Server Memory does not seem to be the issue. Still looking – dbafromthecold Jan 22 '15 at 11:33
  • While not the issue here, it's worth to mention that the Columnstore Object Pool (CACHESTORE_COLUMNSTOREOBJECTPOOL memory clerk type) is outside the buffer pool aswell. See this blog post from Niko Neugebauer – Blaž Dakskobler Feb 3 '17 at 7:50
  • @BlažDakskobler yes, thanks, in-memory too. I'll update the post when I have a chance – Aaron Bertrand Feb 3 '17 at 12:33
3

I got below definition from Bob Dorr about what Max server memory in SQL Server 2012 controls. You can also read Books Online for more details

Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

Memory allocated to for thread stack, Third party DLL, Linked server provider which is other than that of Microsoft(like MySQL.PostgreSQL etc) or any DLL loaded in SQL Server address space which is non SQL Server is allocated outside max server memory. IIRC backup operation in SQL Server 2012 is also still allocated memory outside buffer pool.

Are you using linked server to query other RDBMS? Any other software installed on same windows machine. Can you post on some shared location the output of following queries

select type,
sum(pages_kb)/1024 as [Memory utilized in MB],
sum(awe_allocated_kb)/1024 as [Memory allocated though Windows API]
 from sys.dm_os_memory_clerks
 group by type
 order by [Memory utilized in MB] desc
 Go
-------

 select (virtual_address_space_committed_kb/1024) as virtual_address_space_committed_MB,
 (locked_page_allocations_kb/1024) locked_page_allocations_MB,
 (pages_kb/1024) [memory allocated MB]
  from sys.dm_os_memory_nodes
  Go
-------
SELECT SUM (pages_in_bytes)/1024 as 'KB Used', type 
FROM sys.dm_os_memory_objects
GROUP BY type 
ORDER BY 'KB Used' DESC;
GO
--------
select name,
type,
sum(pages_kb)/1024 as [Mem MB],
sum(entries_count) as [Total Entry count] from sys.dm_os_memory_cache_counters
group by
type, name
order by [Mem MB] desc
Go
-----
select * from sys.dm_os_loaded_modules where company <> 'Microsoft Corporation'
go

Can you also upload complete DBCC MMEMORYSTATUS output on some shared location and post the link here. This would help in understanding what component is taking memory

Edit: As per dbcc memorystatus output i can see 2 NUMA nodes and memory utilized by each node is approx

Node 1 : VM Committed 33554380

Node 2: VM Committed  33554420

Total is approx 64 G. 

Again if you see Memory Manager in memorystatus output its

Memory Manager                           KB
---------------------------------------- -----------
VM Reserved                              260726964
VM Committed                             **67108820**

VM committed is actually Virtual Memory committed by SQL Server and since this memory is committed it has physical memory backing it. This again which makes me think SQL Server is using 65 G as set in max server memory

This is what is max server memory. So memory is well distributed between both nodes can you also add output of below query jut to check. Please add screenshot

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO
  • @DBAFromTheCold: Its late but are you still looking for answer, if yes I want to give one more try :) Can you post complete output of select * from sys.dm_so_process_memory – Shanky Feb 27 '15 at 9:15
  • Hi Shanky, thanks for the response but the issue has resolved itself. Nothing on my part, SQL released the memory on its own. I am monitoring the server and if it occurs again, I will post an update. Really want to get to the bottom of this. – dbafromthecold Mar 2 '15 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.