Interpreting sys.dm_os_sys_memory

by Robert   Last Updated August 30, 2017 11:06 AM

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?

Tags : sql-server dmv


Answers 1


Could it be that you need to take into account the System Cache and Kernel Memory pages?

It would help a lot if you added some actual numbers from your server and what you expect, to your question.

For example, here is my test server's memory state:

memory stats for a test server

The code is:

SELECT 'Page File' AS MemoryType
    ,CONVERT(DECIMAL(10, 2), 1.0 * total_page_file_kb / 1024) AS Total_MB
    ,CONVERT(DECIMAL(10, 2), 1.0 * available_page_file_kb / 1024) AS Free_MB
    ,CONVERT(VARCHAR(5), CONVERT(DECIMAL(10, 1),
        100.0 * available_page_file_kb / total_page_file_kb)) + '%' AS Free
FROM sys.dm_os_sys_memory

UNION ALL

SELECT 'Physical (RAM)'
    ,CONVERT(DECIMAL(10, 2), 1.0 * total_physical_memory_kb / 1024)
    ,CONVERT(DECIMAL(10, 2), 1.0 * available_physical_memory_kb / 1024)
    ,CONVERT(VARCHAR(5), CONVERT(DECIMAL(10, 1),
        100.0 * available_physical_memory_kb / total_physical_memory_kb)) + '%'
FROM sys.dm_os_sys_memory

UNION ALL

SELECT 'Non-Physical'
    ,CONVERT(DECIMAL(10, 2),
         1.0 * (total_page_file_kb - total_physical_memory_kb) / 1024)
    ,CONVERT(DECIMAL(10, 2),
         1.0 * (available_page_file_kb - available_physical_memory_kb) / 1024)
    ,CONVERT(VARCHAR(5), CONVERT(DECIMAL(10, 1),
        100.0 * (available_page_file_kb - available_physical_memory_kb)
              / (total_page_file_kb - total_physical_memory_kb))) + '%'
FROM sys.dm_os_sys_memory

UNION ALL

SELECT 'System Cache',CONVERT(DECIMAL(10, 2), 1.0 * system_cache_kb / 1024),NULL,NULL
FROM sys.dm_os_sys_memory

UNION ALL

SELECT 'Kernel: Paged',CONVERT(DECIMAL(10, 2), 1.0 * kernel_paged_pool_kb / 1024),NULL,NULL
FROM sys.dm_os_sys_memory

UNION ALL

SELECT 'Kernel: Non-Paged'
    ,CONVERT(DECIMAL(10, 2), 1.0 * kernel_nonpaged_pool_kb / 1024),NULL,NULL
FROM sys.dm_os_sys_memory
ORDER BY Total_MB DESC
Oreo
Oreo
August 30, 2017 10:27 AM

Related Questions


creating index on big table(s)

Updated September 03, 2018 18:06 PM

Linked Server Error on SQL Agent Job

Updated March 16, 2016 08:02 AM