How can I extract the perfmon counter "Memory: Pages/Sec" using t-sql?

Question

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 :)

Aaron Bertrand · Answer 1 · 2016-01-29T00:13:44.150

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.

score 1 · Answer 2 · edited Apr 13 '17 at 12:42

Ideally / in the long-term, I do agree with @Aaron (comment on the Question) about contacting SolarWinds to request that they create a custom metric for this. But even if you do that and they accept the idea, it could take a while for them to release it. In the mean time, or if you ever just want to view this info in an ad hoc fashion, you can try the following:

IF that metric really is the same as the "Page Faults" metric as reported by DBCC MEMORYSTATUS ^{(Please see Notes at the bottom)}, then you can do something like the following:

DECLARE @BeginningValue TABLE
(
  [Counter] NVARCHAR(100) NOT NULL,
  [Value] BIGINT NOT NULL,
  [InsertTime] DATETIME NOT NULL DEFAULT (GETDATE())
);
DECLARE @EndingValue TABLE
(
  [Counter] NVARCHAR(100) NOT NULL,
  [Value] BIGINT NOT NULL,
  [InsertTime] DATETIME NOT NULL DEFAULT (GETDATE())
);
---

INSERT INTO @BeginningValue ([Counter], [Value])
  EXEC ('DBCC MEMORYSTATUS;');

WAITFOR DELAY '00:00:10.000'; -- 10 second pause

INSERT INTO @EndingValue ([Counter], [Value])
  EXEC ('DBCC MEMORYSTATUS;');
---

DELETE FROM @BeginningValue WHERE [Counter] <> N'Page Faults';
DELETE FROM @EndingValue WHERE [Counter] <> N'Page Faults';

SELECT lst.Value AS [EndingValue], frst.Value AS [BeginningValue],
       lst.InsertTime AS [EndingTime], frst.InsertTime AS [BeginningTime],
       (lst.Value - frst.Value) AS [Difference],
       DATEDIFF(SECOND, frst.InsertTime, lst.InsertTime) AS [NumSeconds],
       CONVERT(DECIMAL(14, 4),
               (((lst.Value - frst.Value) * 1.0) /
                        DATEDIFF(SECOND, frst.InsertTime, lst.InsertTime))
              ) AS [PageFaults/Sec]
FROM   @BeginningValue frst
CROSS JOIN @EndingValue lst;

Returns:

Ending  Beginning  EndingTime               BeginningTime            Diff  Sec.  Faults/Sec
418529  418497     2016-01-28 13:13:27.850  2016-01-28 13:13:17.827  32    10    3.2000

Notes

If you are on SQL Server 2008 or newer, then please use the sys.dm_os_process_memory DMV (as pointed out in @Aaron's answer), else you can use DBCC MEMORYSTATUS as I have shown above. I had only recommended DBCC MEMORYSTATUS (and hence this method of capturing the info it returns) due to overlooking that field being returned from any DMV. But I have tested them both and they return the same value, so there is really no good reason to use DBCC MEMORYSTATUS given the extra work entailed if you have access to the sys.dm_os_process_memory DMV.
I had not elaborated on this in my initial posting of this answer, but 10 seconds between samples is actually rather low and will likely decrease the accuracy of the resulting metric. Ideally a larger number -- at least 30, if not 60, seconds -- would be used. However, the concern is that having a monitoring tool call this code (I assumed it would be placed into a Stored Procedure) introduces a potential risk in the monitoring software considering the delay to be a timeout, or somehow delays its processing or collection of other metrics. Hence, I used 10 seconds as a means of favoring reliability over accuracy. This goes back to what I said at the very beginning about agreeing with @Aaron's position of the ideal scenario being that the software vendor provides a native / built-in means of collecting this information.

How can I extract the perfmon counter "Memory: Pages/Sec" using t-sql?

2 Answers2