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.