SQL memory - view used memory and available memory

Question

I did some research and came across several different ways to find out how much memory SQL Server is actually using (since the task manager is not accurate because SQL will reserve as much memory as it can for itself)

I found these two resources most useful:

How much RAM is SQL Server actually using?

How to parse "dbcc memorystatus" in SQL Server 2008

However, the struggle I am experiencing is that when I try to use both methods on the same server, I have problems with the correlation of numbers.

I get very different results from performance counters (SQL memory manager - shared server memory and destination server memory) compared to the query “dbcc memorystatus” of VM Reserved and VM Committed.

Both appear to be listed in “KB,” but the numbers are not close to each other. Maybe I don’t look at the correct results (maybe VM reserved / commit is not a reading of physical memory, but a counter? But then in this case, where in the SQL query it shows the amount of memory, SQL uses / reservation, since neither one of the results does not look like numbers that correspond to performance counters)

If anyone can give me some recommendations, bearing in mind that I am not very experienced in monitoring SQL performance, I would really appreciate it.

Thanks in advance.

Answer 1

Looking at the Perfmon counters for the total server memory and the target server memory, you get the size of the buffer pool cache, which is a subset of the shared memory used by SQL Server. This is usually the largest single memory pool. For example, on my dev server I have a total server memory of 2759 MB. You can use the following query to print this number:

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 other hand, displays a lot of information. If you scroll about 3/4 of the way down, you will see a result set with columns named "Buffer Pool" and "Value", with the first buffer pools called "Committed" and "Target". These two values ​​are listed as 8 KB pages, so multiply the value by 8192 to get the result in bytes, and then divide it by 1048576 to get the result in MB. On my dev server I have 353,230 pages or 2759 MB.

The first result of DBCC MEMORYSTATUS gives you the amount of virtual memory (VM) reserved and allocated by SQL Server. I would ignore Reserved as it is not a good indicator of how much memory is actually being used (which means Committed). Also, Reserved is slightly higher than the total physical memory on all my servers. If you have a value for "Locked Pages Allocated", then your "VM Committed" value is likely to be around 400 MB. AWE and blocked pages are not counted in the cost of the commit, so the task manager shows the wrong value.

You do not need to go too deep into DBCC MEMORYSTATUS unless you have a specific memory management problem. My rule of thumb is to properly configure your buffer pool so that your free physical memory is low to avoid memory loss (~ 1-2 GB, but Windows will always try to save 128-256 MB of physical memory), and your “peak” Commit Charge "(from the task manager) never exceeds the physical amount of memory on the server.