A new tool was added to the belt recently - I like the idea. Since SQL Server will respond to server level low memory messages, I like being able to see the state of server memory. For that has Microsoft wrought sys.dm_os_memory.
sys.dm_os_sys_memory (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact-sql
There's good stuff in there for investigation of OOM scenarios.
Let's see what turns up on my laptop.
Based on the column names and values above, seems natural to think:
total_page_file_kb - available_page_file_kb = used page file kb
11027476 kb - 3047668 kb = 7979808 kb
Holy cow! Is my laptop using nearly as much paging space as there is RAM on the laptop??
Weird. If something forced that much paging space use relative to RAM on the laptop... I certainly wouldn't expect system_memory_state_desc = 'Available physical memory is high'!!
Lets double-check by using wmic in an administrator cmd.exe.
OK... its clear already something is afoot. AllocatedBaseSize is the current size of the pagefile indicated in 'Description', in mb. So... 2880 mb is the size of the pagefile. And only 1684 mb currently used, with a peak usage of 2752 mb. Almost 8 gb of pagefile used as reported by SQL Server sys.dm_os_sys_memory? Nope - no way.
So what's up? Sometimes memory accounting in SQL Server takes some work. 😀😀😀
I stared at the numbers for a bit and had a hunch... double-checked and it tied out.
11027476 kb - 8077528 kb = 2949948 kb ≈ 2880mb
Hmm.
So:
total_page_file_kb - total_physical_memory_kb =AllocatedBaseSize
Aha! Looks like total_page_file_kb column is misnamed. Because...
total_physical_memory_kb + AllocatedBaseSize = total_page_file_kb
So total_page_file_kb is really more like 'total virtual memory kb'!
OK. Let's look back at the definitions in BoL.
sys.dm_os_sys_memory (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact-sql
The description of available_page_file_kb is wrong. Since total_page_file_kb is described as the commit limit - or the virtual memory as sum of physical memory + paging space - the available_page_file_kb is then not best defined as an available portion of the paging file but as an available portion of the commit limit or virtual memory.
All right - enough for now. See you again soon!
Genius
ReplyDelete