I'm trying to figure out a way(using only TSQL) to determine if and how much the PAGEFILE is used (I will capitalize this term if I refer to the windows page file from now on - like pagefileusage% counter).
My first thought is using sys.dm_os_sys_memory. I am a little bit confused here. The description of the total_page_file_kb is that this is a commit limit, so basically, it's the size of the physical memory (RAM) + size of the PAGEFILE. So far it is ok. But what exactly is the available_page_file_kb? Is it referring to the commit limit or to the PAGEFILE? I tried to use these calculations:
Assuming that available_page_file_kb referring to the commit limit:
PAGEFILE usage% = (available_page_file_kb-available_physical_memory_kb) / (total_page_file_kb-total_physical_memory_kb)
Assuming that available_page_file_kb is referring to the PAGEFILE
PAGEFILE usage% = (available_page_file_kb) / (total_page_file_kb-total_physical_memory_kb)
Unfortunately none of these calculations show me the right results. I keep checking the pagefileusage% counter on some of my test servers, and sometimes the first formula is wrong by only 1-3% and the second one by 15-25% or vice versa, the second one by 1-3% and the first one by 15-25%,
What did I miss here? Is it impossible to determine (or even just get a close estimate) the pagefileusage% with sys.dm_os_sys_memory dmv?