Mitch Wheat - Treat the cause, not the symptoms!

SQL Server contains many Dynamic Management Views (DMV) for diagnosing server activity and health, including one for examining performance counters, sys.dm_os_performance_counters. Some of the counters in "sys.dm_os_performance_counters" with names ending with ‘/sec’ (such as like "Page lookups/sec", or "Page writes/sec") are known as "per second" counters. When you first come across this DMV you might not realise that the values for these counters are cumulative and require two measurements over an interval and then calculating the difference in those values based on the time between the two measurements.

Here is a simple TSQL snippet to perform this calculation for all per second counters in sys.dm_os_performance_counters:

-- Get first sample
SELECT *,  getdate() as sampletime, 
ROW_NUMBER() OVER (order by [object_name], instance_name, [counter_name]) as rowid
INTO #tempCounters1
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%/sec%'
order by [object_name], instance_name, [counter_name]; 

-- Wait for 10 seconds
WAITFOR DELAY '00:00:10'; 

SELECT *,  getdate() as sampletime, 
ROW_NUMBER() OVER (order by [object_name], instance_name, [counter_name]) as rowid
INTO #tempCounters2
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%/sec%'
order by [object_name], instance_name, [counter_name]; 

-- Calculate per second values
SELECT t1.[object_name], t1.instance_name, t1.[counter_name],
(t2.cntr_value - t1.cntr_value) / datediff(ss,t1.sampletime,t2.sampletime) 
FROM #tempCounters1 t1 join #tempCounters2 t2 ON t1.rowid = t2.rowid; 

DROP TABLE #tempCounters1
DROP TABLE #tempCounters2
GO 

Here is a Table valued function version which accepts an interval parameter, and is easy to filter:

IF OBJECT_ID(N'dbo.PerSecondPerformanceCounters', N'TF') IS NOT NULL
    DROP FUNCTION dbo.PerSecondPerformanceCounters;
GO 

CREATE FUNCTION PerSecondPerformanceCounters(@intervalseconds int)
RETURNS @retCounterInformation TABLE
(
    [object_name] nchar(128) not null,
    [counter_name] nchar(128) not null,
    [instance_name] nchar(128) not null,
    [cntr_value] bigint not null
)
AS
BEGIN 

DECLARE @tempTable1 TABLE
(
    [object_name] nchar(128) not null,
    [counter_name] nchar(128) not null,
    [instance_name] nchar(128) not null,
    [cntr_value] bigint not null,
    [sampletime] datetime not null,
    [rowid] int not null
) 

DECLARE @tempTable2 TABLE
(
    [object_name] nchar(128) not null,
    [counter_name] nchar(128) not null,
    [instance_name] nchar(128) not null,
    [cntr_value] bigint not null,
    [sampletime] datetime not null,
    [rowid] int not null
) 

-- Take first sample 

INSERT INTO @tempTable1
SELECT [object_name], 
       [counter_name], 
       [instance_name],
       [cntr_value],
       getdate() as sampletime, 
       ROW_NUMBER() OVER (order by [object_name], [counter_name], [instance_name]) as rowid
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%/sec%'
ORDER BY [object_name], [counter_name], [instance_name]; 

-- Wait for specified time

declare @start datetime = getdate()
while (1=1)
BEGIN
  if datediff(ss, @start, getdate()) >= @intervalseconds
     BREAK;
END 

-- Take second sample
INSERT INTO @tempTable2
SELECT [object_name], 
       [counter_name], 
       [instance_name],
       [cntr_value],
       getdate() as sampletime, 
       ROW_NUMBER() OVER (order by [object_name], [counter_name], [instance_name]) as rowid
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%/sec%'
ORDER BY [object_name], [counter_name], [instance_name]; 

---- calculate per second values (ignore fractional values)
INSERT @retCounterInformation
SELECT 
t1.[object_name], 
t1.instance_name, 
t1.[counter_name], 
(t2.cntr_value - t1.cntr_value) / datediff(ss,t1.sampletime,t2.sampletime) AS cntr_value
FROM @tempTable1 t1 
join @tempTable2 t2 ON t1.rowid = t2.rowid; 

RETURN; 

END
GO 

Here is how you would call it:

 

-- Return only per second counters with non-zero values 

select * from PerSecondPerformanceCounters(10)
where cntr_value > 0 

-- Return non-zero counter values for specified objects only

select * from PerSecondPerformanceCounters(10)
where cntr_value > 0 AND
    (object_name = 'SQLServer:Buffer Manager' OR object_name = 'SQLServer:Access Methods')