4

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?

  • Maybe this article helps on clarifying the counter itself? Are you using the right definition? blogs.technet.com/b/perfguru/archive/2008/01/08/… – NickyvV Mar 25 '15 at 12:18
  • You said Assuming that available_page_file_kb referring to the commit limit and you also said 8Assuming that available_page_file_kb is referring to the PAGEFILE* what are you actually trying to find out. Do you want to find out SQL Server memory paged or Windows memory paged ? – Shanky Mar 25 '15 at 16:08
  • @Shanky: paging file % usage performance counter is an essential counter for servers which hosts SQL Server services. The smaller this number the better for SQL Server. But this counter is OS related so it cannot be selected via sys.dm_os_performance_counters. I would like to find a way to get this counter's value (or close estimate) from inside SQL Server using TSQL. – Robert Mar 26 '15 at 10:08
  • Are you sure you aren't running into this due to rounding? How many decimal points are being used? – stacylaray Feb 22 '16 at 2:20
  • @stacylaray: I think it is not rounding issue. The formula divides two positive integer. Rounding could cause only +/- 1 deflection top. – Robert Feb 28 '16 at 9:03
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.