SQL Server 2012 Standard Edition using more than 64GB of memory, why and how?

Question

I put SQL Server 2012 Standard on a virtual machine (VMWare) which has roughly 98GB of Memory.

Now I checked the query below how much memory is taken by SQL Server.

Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 

And see that he ate already 81GB of the memory and the max Memory is set to ~90GB.

Using:

select (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,      
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB, 
(total_virtual_address_space_kb/1024 ) VAS from sys. dm_os_process_memory

SQL Server 2012 Standard should support only 64GB of memory.

Why does my SQL Server eat more than he can? Or is the info incorrect?

Any idea? Is that related to a virtual machine using virtual memory?

I don't know if balloon driver is activated on VM Host...

If you need any more info I will add it.

Answer 1

The 64GB limit (and 128GB in 2014) applies only to buffer pool. The KB article "Memory configuration and sizing considerations in SQL Server 2012" says:

Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool).

And as we all know, max server memory also does not control all of SQL Server's memory. The official documentation is in the process of being updated to provide the following guidance on this (this exact wording was provided by Bob Ward):

The server-level max server memory setting 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 sys.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.

So, it is quite feasible that you will see sqlservr.exe use more than 64GB of memory if there is significant allocation to anything other than the buffer pool, even in Standard Edition (and more than 1GB even in Express Edition).

If you want to stop SQL Server from using more than 64GB of memory, you'll need to set max server memory to something less than 64GB (you may need to experiment here to find the right mix, but understand that the non-buffer pool memory can fluctuate, so you may still see things that venture beyond both the licensing limit and the configured limit).

Or, you can just leave it as is, and be thankful that you can use up to 64GB for buffer pool and plenty else for other things that might otherwise cut into buffer pool usage if the 64GB limit were an enforced hard cap.

---

You can see how memory is distributed in various ways. Among the memory clerks, you can use:

SELECT [type], mem_MB = pages_kb/1024
 FROM sys.dm_os_memory_clerks
 ORDER BY pages_kb DESC;

In most cases, the top consumer is going to be MEMORYCLERK_SQLBUFFERPOOL, but you should see significant volume in CACHESTORE_* entries as well. You can group it at a higher level using something like:

SELECT [type] = COALESCE(SUBSTRING([type],1,CHARINDEX('_',[type])-1),'-- total'),
  SUM(pages_kb/1024)
FROM sys.dm_os_memory_clerks
GROUP BY GROUPING SETS((),SUBSTRING([type],1,CHARINDEX('_',[type])-1));

To see how cache/user store memory is distributed:

SELECT [type], name, 
 mem_MB = CONVERT(DECIMAL(19,2),pages_kb/1024.0) 
 FROM sys.dm_os_memory_cache_counters
 ORDER BY pages_kb DESC;

Currently, on my lowly VM, the query in your question (provided by @Shanky) says that SQL Server is using 2,085 MB of memory (with 2,004 MB locked in memory). However my query above against the memory clerks DMV says that the total memory being used there is only 1,793 MB.

The query in Chris' answer says that the total is 2,054 MB, with a target of 3,072 MB (this is because I have explicitly set max server memory to 3,072 MB).

So where is the extra ~200 MB? Who knows. (I can also see this difference if I compare total server memory to Process > sqlservr > Private Bytes in Performance Monitor.) I could start parsing the output of DBCC MEMORYSTATUS to try and piece it together (and Tim Chapman wrote some PowerShell to make that easier), but is it worth it? Is all memory even reported there? I'm not 100% certain that it is (or that you should use the output at all in a NUMA system), or that any DMV can give a fully accurate picture of all of the memory being used by SQL Server. I also don't believe all the memory clerk types are even documented anywhere - so even if that gave a complete picture, you might still be scratching your head over what the results really mean.

Task Manager, meanwhile, says sqlservr.exe is only using 42 MB. Whatever you do, please stop using Task Manager. For anything, really. I know you have removed this detail from your question in your most recent edit, but I can't stress enough how useless it is for showing SQL Server memory usage.

Answer 2

Per Aaron Bertrand's answer (Quick look at how much RAM is allocated to SQL Server?). Task Manager shouldn't be used to ascertain how much memory SQL is actually using.

I prefer to use the following query (Again, thanks Aaron):

SELECT object_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

Alternatively, using DBCC MEMORYSTATUS will yield this information (as well as a slew of additional information).

Answer 3

I find that this query, gives me a more accurate total actual memory in use -

set transaction isolation level read uncommitted
select 
    @@Servername,
    osn.node_state_desc, 
(
select physical_memory_in_use_kb/(1024) as SQLMEMORYINUSE_MB
from sys.dm_os_process_memory
) + (omn.locked_page_allocations_kb / 1024.0 ) as Tot
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'
and osn.node_id = 1

I appreciate the post is old, but this may be of use.

I have an instance of 2012 std, which is using some 120Gb ram in total