Skip to content

sys.dm_os_performance_counters: Part 2

August 13, 2009

Rather than hold up the original thread, I’ll continue with the next counter type, and will periodically revisit my last post to amend the list of counters.

3. Cntr_Type = 272696576

This type of counter is similar to 65792 in that it does not need a base value to compare against. Whatever value you see for one of these counters is the value at the exact moment in time the counter was polled. The trick with these counters are that they are time-based. In order to get the true value of a counter, you need to poll it, wait, then poll it again and compare the results.

All of these counters are cumulative, which means that you subtract the counter’s value at the first poll from the value at the second poll to get the difference over the duration. At that point I recommend that you also divide the difference by the number of elapsed seconds, to normalize the value in a “units per second” metric. In fact, nearly all of the counters of this type are named “xxx/sec”.

To illustrate, let’s look at the SQLServer:Buffer Manager / Page lookups/sec counter. On my system right now it shows 3047164. Oops, wait a sec. Now it shows 3129361. The difference is 82197. About 10 seconds had elapsed between the queries, so the normalized counter value is 8219.7 page lookups per second in that interval.

-- Poll a specific counter for 10 seconds
DECLARE @cntrvalue1 BIGINT, @cntrvalue2 BIGINT, @Duration INT
SET @Duration = 10

SELECT @cntrvalue1 = cntr_value FROM sys.dm_os_performance_counters
   WHERE object_name = 'SQLServer:Buffer Manager' AND counter_name = 'Page lookups/sec'
WAITFOR DELAY @Duration
SELECT @cntrvalue2 = cntr_value FROM sys.dm_os_performance_counters
   WHERE object_name = 'SQLServer:Buffer Manager' AND counter_name = 'Page lookups/sec'

SELECT 'Average counter value: ', CAST(@cntrvalue2 - @cntrvalue1 AS FLOAT) / @Duration

So how long do you wait between polling intervals? To me, that depends on your reasons for monitoring the server in the first place. If you’re looking to track a specific performance problem and you have your server under the microscope, you probably want to poll every 5 seconds or less. If you’re more interested in long-term trends, polling every 15 minutes or even every hour is probably fine.

If you’re logging these counters to a table like I did, the really cool thing is that you get to decide later. I’m more interested in long-term performance so I set up a task to log every 5 minutes. To get the average counter value over a 5-minute interval, I just subtract one poll from the next and divide by 300. But from the same data I can also get the values, say, hourly over the course of the day by subtracting two values 12 polls apart and dividing by 3600. So it’s really flexible. The key is to log the actual value of the counter at each poll, and not to do any arithmetic on it before logging.

I’ll save the next counter type for part III.

Advertisements

From → PerfCounter DMV

Comments are closed.

%d bloggers like this: