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.
|\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".
FIX: Out of memory error when the virtual address space of the SQL Server process is very low on available memory
That's all for now, folks.
Post a Comment