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.
