Total Memory used by SQL Server (64 bit)

Question

My knowledge on the subject suggests that perf counter SQL Server:Memory Manager: Total Server Memory only gives you buffer pool memory. There is a column called physical_memory_in_use in a DMV named sys.dm_os_process_memor that gives you physical working set.

But I am not sure ...

is there a DMV or perf counter that could tell me total (buffer pool + non buffer pool i.e. MemToLeave) physical memory used by SQL Server process for 64 bit SQL Server 2008 R2 and SQL 2012 running on 64 bit Windows OS?

Answer 1

I'm not sure about a single view for memory, but you can get the information from two queries. The first (taken from Glenn Berry's DMV queries) leverages sys.dm_os_buffer_descriptors and will show you buffer pool by database:

SELECT 
    DB_NAME(database_id) AS [Database Name]
    ,CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id not in (1,3,4) -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

The second query I put together by looking at sys.dm_os_memory_cache_entries to show me non-buffer pool information:

select 
    name
    ,sum(pages_allocated_count)/128.0 [Cache Size (MB)]
from sys.dm_os_memory_cache_entries
where pages_allocated_count > 0
group by name
order by sum(pages_allocated_count) desc

There are a host of DMVs related to memory, notated by dm_os_memory_*. You can use these to investigate how memory is used by SQL Server on a variety of levels.

Answer 2

I came across this solution suggested by Joe Sack (SQLSkills now)

Find Non-Buffer Pool Memory (MemToLeave) in "Private Bytes"

It appears(just tested and seems right) to me that on 64 bit Process: sqlservr: Private Bytes" can give you total memory used by SQL Server (buffer pool + non buffer pool) considering lock pages in memory is not enabled.

If lock pages in memory is enabled than private bytes give you total non buffer pool memory used by SQL server process.