SQL memory - viewing actual memory used and Memory available

Question

I've done some research and came across a few different ways to see how much memory SQL Server is actually using (since Task manager isn't accurate, as SQL will reserve as much memory as it can for itself)

I found these two resources to be the most useful:

How much RAM is SQL Server actually using?

How to analyze 'dbcc memorystatus' result in SQL Server 2008

However, the struggle I'm having is that when I try both methods on the same server, I'm having trouble correlating the numbers.

I get very different results from the performance counters (SQL memory manager -- total server memory and target server memory) compared to query "dbcc memorystatus" VM Reserved and VM Committed.

They both appear to be listed in "KB" yet the numbers are not anywhere close. Maybe I'm not looking at the right results (maybe VM reserved / committed isn't a physical memory reading and the counter is? But then in this case, where in the SQL query does it show the amount of memory SQL is using / reserving , as none of the results seem like numbers that correspond with the performance counters)

If anyone can give me some guidance, bearing in mind I'm not very experienced in SQL performance monitoring, I'd really appreciate it.

Thank you in advance.

Answer 1

Looking at the Perfmon counters for Total Server Memory and Target Server Memory will give you the size of the buffer pool cache, which is a subset of the total memory being used by SQL Server. It's usually the largest single pool of memory though. For example, on my dev server, I have Total Server Memory of 2759 MB. You can use the following query to pull this number out:

SELECT object_name, counter_name, cntr_value AS 'Total Server Memory (KB)'
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Total Server Memory (KB)'

DBCC MEMORYSTATUS, on the otherhand, displays a lot of information. If you scroll about 3/4 of the way down, you'll see a resultset with columns named "Buffer Pool" and "Value", with the first buffer pools being named "Committed" and "Target". These two values are listed as 8 KB Pages, so multiply the value by 8192 to get a result in bytes, and then divide by 1048576 to get a result in MB. On my dev server, I have 353,230 pages, or 2759 MB.

The first resultset of DBCC MEMORYSTATUS gives you the amount of Virtual Memory (VM) reserved and committed by SQL Server. I would ignore Reserved, as it's not a good indicator of how much memory is actually in use (that's what the Committed value is). Also, Reserved is quite a bit higher than the total physical memory in all of my servers. If you have a value for "Locked Pages Allocated", then your "VM Committed" value is likely to be about 400 MB - AWE and Locked Pages don't count in the commit charge, which is why Task Manager shows an incorrect value.

You shouldn't need to dig too deep into DBCC MEMORYSTATUS unless you have a specific problem with memory management. My rules of thumb is to size your buffer pool appropriately, have your available physical memory is low to avoid wasted memory (~1-2 GB, but Windows will always try to keep 128-256 MB physical memory available), and your "Peak Commit Charge" (from Task Manager) is never higher than the physical amount of memory in the server.