5

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

10
  • 2
    Pages of what per second? SQL Server exposes evictions this way, as well as readahead, checkpoint, background writer, pages allocated, and pages compressed. But not just a generic pages/sec, what would this actually mean within SQL Server and how would you troubleshoot it? Jan 28, 2016 at 16:49
  • 1
    THere is a generic script on database journal that will serve as a good starting point.
    – Kin Shah
    Jan 28, 2016 at 16:52
  • 1
    @AaronBertrand: I'm looking for the "Memory: Pages/Sec" perfmon counter that indicates there is hard page faulting.
    – Chinesinho
    Jan 28, 2016 at 17:08
  • 1
    sys.dm_os_performance_counters only exposes to SQL Server the metrics that are relevant to that instance. You'll need to get generic operating-system level metrics elsewhere. Jan 28, 2016 at 17:09
  • 1
    @AaronBertrand: so would it even be possible to get OS-level metrics via t-sql? Or would I need to use powershell to tap into WMI?
    – Chinesinho
    Jan 28, 2016 at 17:13

2 Answers 2

2

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.

1

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.

3
  • 1
    Well that's a lot of work compared to SELECT page_fault_count FROM sys.dm_os_process_memory;. :-) I think the issue is still whether Ignite supports a SQL statement (or a batch like the one you show) as a custom metric. It might be very easy to plug it in, but it might not. What it more likely is that it can monitor the output of a simple query, and keep track of deltas between each poll. Not many monitoring tools will encourage you to put arbitrary waitfors etc. into their code path. Jan 28, 2016 at 21:52
  • @AaronBertrand True. I looked through some DMVs earlier and didn't see it which is why I went with the DBCC. The silver-lining for that effort (if any) is that dm_os_process_memory does not seem to be available in SQL Server 2005, so this still could prove helpful to some ;-) I will make a note about these in my answer after dinner. Jan 28, 2016 at 23:21
  • @AaronBertrand Answer has been updated. Jan 29, 2016 at 3:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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