Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
There are queries that can tell you lot about memory allocation for SQL Server like one here blogs.msdn.com/b/sqlsakthi/archive/2011/02/28/… but looking for a counter or dmv that can tell me total physcial memory usage (buffer pool + memtoleave) by SQL server. –  SQL Learner May 6 '13 at 16:01
    
Tim Chapman also offers a way to parse DBCC MEMORYSTATUS using Powershell: blogs.msdn.com/b/timchapman/archive/2012/08/16/… –  SQLRockstar May 7 '13 at 3:41
    
Tom, I was listening to your PASS 2011 session on memory management last night. For e.g. we have linked server to ORACLE and SQL loads ORACLE provider for OLDEB etc. DLLs during the startup and I believe it uses memory outside buffer pool for these. Is there a way we know how much memory SQL is actually using? –  SQL Learner May 7 '13 at 13:32
    
I use this to track physical memory for my databases. ------------------------------------------------------------------------- SELECT total_physical_memory_kb, available_physical_memory_kb, total_page_file_kb, available_page_file_kb, system_memory_state_desc FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE); –  avakharia May 10 '13 at 2:59
    
@avakharia That shows memory usage on the server level and not necessarily SQL Server's memory usage. It is useful information, but doesn't show buffer pool and non-buffer pool usage. –  Mike Fal May 10 '13 at 4:14
show 1 more comment
up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.