Sql-server – SQL Server 2012 memory consumption outside the buffer pool

memorysql serversql-server-2012

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.

Best Answer

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

Related Question