Sql-server – How to extract the perfmon counter “Memory: Pages/Sec” using t-sql

monitoringperfmonsql serversql server 2014

I've googled this and so far, no luck so turning to all you awesome gurus for help.

I'm trying to figure out if there's a way to get the perfmon counter "Memory: Pages/Sec" using t-sql as I'm trying to add it as a custom metric in our Ignite monitoring tool. I've looked at the sys.dm_os_performance_counters DMV but didn't find it there.

Anyone know of a way doing this? Thanks in advance 🙂

Best Answer

Following @Srutzky's example of just looking at page faults/sec (not pages/sec), you can do this:

DECLARE @before BIGINT, @after BIGINT;

SELECT @before = page_fault_count FROM sys.dm_os_process_memory;

WAITFOR DELAY '00:00:10';

SELECT @after = page_fault_count FROM sys.dm_os_process_memory;

SELECT PageFaultsPerSec = (1.0*@after - @before)/10.0;

It is likely that you can just have Ignite poll for the following metric:

SELECT page_fault_count FROM sys.dm_os_process_memory

And then set up alerts based on thresholds or deltas. But I still recommend you contact them to find out how to best implement a custom metric based on a column from an arbitrary DMV, which might be different from the perf counters DMV.

Related Question