Interpreting SQL Server 2019 DBCC MEMORYSTATUS output -- Part 1

Recently someone asked me for help interpreting DBCC MEMORYSTATUS output.  And it's taken me too doggone long to start answering their questions.  Sorry - I'll start with this and keep going.

In response to certain OOM conditions, SQL Server will automatically log DBCC MEMORYSTATUS output to the error log.  Here I'll look at output from the command run via a task scheduler script on a 5 minute schedule.

One issue with the excerpt of the output below is the lack of units.

start Fri 12/13/2019 14:40:00.80 
Process/System Counts                                Value               
---------------------------------------------------- --------------------
Available Physical Memory                                    250846224384
Available Virtual Memory                                  136302950027264
Available Paging File                                        298663870464
Working Set                                                   13835464704
Percent of Committed Memory in WS                                      99
Page Faults                                                       6876485
System physical memory high                                             1
System physical memory low                                              0
Process physical memory low                                             0
Process virtual memory low                                              0
~~~~~
end   Fri 12/13/2019 14:40:06.62

Here's some info from perfmon.

Time	14:40:01	14:40:06	14:40:11
\NUMA Node Memory(_Total)\Total MBytes	952319	952319	952319
\NUMA Node Memory(_Total)\Free & Zero Page List MBytes	233635	233634	233634
\NUMA Node Memory(_Total)\Available MBytes	239222	239221	239229
\Paging File(_Total)\% Usage	0	0	0

MemoryStatus gives Available Physical Memory = 250846224384.  Assuming that number is specified in bytes, dividing by 1048576 yields  megabytes: 239225.6 mb. That's no more than 4.6 mb away from the 3 values for [\NUMA Node Memory(_Total)\Available MBytes] shown in the perfmon above.

Below "wmic pagefileset list" shows the lone pagefile with a size of 48000 mb. Perfmon above shows it at 0% used.

total_physical_memory_kb     =  975175156 kb = 930 gb
available_physical_memory_kb =  243809076 kb = 238095 mb
total_page_file_kb           = 1024327156 kb = 930 gb + 48000 mb
available_page_file_kb       =  289622644 kb = 234835 mb + 48000 mb

So what have we got?  MEMORYSTATUS [Available Physical Memory] measured in bytes appears to be the same resource as perfmon [\NUMA Node Memory(_Total)\Available MBytes] and sys.dm_os_sys_memory.available_physical_memory_kb.

MEMORYSTATUS [Available Paging File] measured in bytes appears to be free paging file plus MEMORYSTATUS [Available Physical Memory]. MEMORYSTATUS [Available Paging File] appears to be the same resource as sys.dm_os_sys_memory.available_page_file_kb.

What about MEMORYSTATUS [Available Virtual Memory]?  It seems so much higher than other measures.  Pulling in the MEMORYSTATUS value from above...


Available Virtual Memory = 136302950027264 B = 123.97 TB


Here's a similar number...

 total_virtual_address_space_kb = 137438953343 kb = 128 TB

The value from dm_os_process_memory was retrieved several days after the other values in this blog post.  *And* after a SQL Server service restart.

This KB article explains that SQL Server process virtual address space starts at approximately 8TB in Windows Server 2012.  But it also mentions "Windows 2012 R2 [and above] allows virtual address space to grow as large as 128 TB".

kb3074434
FIX: Out of memory error when the virtual address space of the SQL Server process is very low on available memory
https://support.microsoft.com/en-us/help/3074434/


That's all for now, folks.