SQL Server 2012 memory consumption outside the buffer pool

Question

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:-

And here'e the set 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:-

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

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

Answer 1

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:

• Troubleshooting SQL Server Memory

Some good background in another article on that site too:

• SQL Server 2012 Memory

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):

• MTL Consumers

Answer 2

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