Skip to content

sys.dm_os_performance_counters: Realtime or Cumulative?

November 24, 2010

After publishing my initial posts on this subject, I became suspicious of several “realtime” counters that appeared to always increase as time went on. They never decreased! The first one that I noticed this behavior was “Databases:Log Growths” which, for one particular server I was monitoring, was always a non-zero value. I quickly realized that the value, although not zero, was not changing either.

I figured where there was smoke there was probably fire, and so I built a script to analyze all of the realtime counters across the 50-odd instances of SQL that I monitor. Below are my findings. Unfortunately I was not able to confirm or deny all counters (140 of them!), as some of them remained stubbornly at zero for the entire polling interval. Others changed, but so infrequently that I could not determine with 100% certainty which type they are. So, there is still room for improvement.

 

Not Realtime

In any case, first is a list of the realtime counters that I confirmed to really be cumulative:

Counter Object Counter Name
SQLServer:Broker Activation Task Limit Reached
SQLServer:Broker Statistics Broker Transaction Rollbacks
SQLServer:Broker Statistics Dropped Messages Total
SQLServer:Broker Statistics Enqueued Local Messages Total
SQLServer:Broker Statistics Enqueued Messages Total
SQLServer:Broker Statistics Enqueued Transport Msg Frag Tot
SQLServer:Broker Statistics Enqueued Transport Msgs Total
SQLServer:Broker Statistics SQL RECEIVE Total
SQLServer:Broker Statistics SQL SEND Total
SQLServer:CLR CLR Execution
SQLServer:Cursor Manager Total Cursor flushes
SQLServer:Databases Log Growths
SQLServer:Databases Log Shrinks
SQLServer:Databases Log Truncations
SQLServer:Transactions Version Store unit creation
SQLServer:Transactions Version Store unit truncation

           

“Probably” Not Realtime

I tagged these six counters as “probably” not realtime based on the word “total” in their name, but I did not find any supporting evidence as these counters were zero on all of my servers.

SQLServer:Broker Statistics Activation Errors Total
SQLServer:Broker Statistics Corrupted Messages Total
SQLServer:Broker Statistics Forwarded Messages Total
SQLServer:Broker Statistics Forwarded Msg Byte Total
SQLServer:Broker Statistics Forwarded Msg Discarded Total
SQLServer:Cursor Manager Total Async population count

These next four counters were cumulative on most of my servers, but exhibiting realtime behavior on a few others, so I have to include them in the “probably” bucket as well.

SQLServer:Broker/DBM Transport Current Bytes for Send I/O
SQLServer:Broker/DBM Transport Current Msg Frags for Send I/O
SQLServer:Broker/DBM Transport Pending Bytes for Send I/O
SQLServer:Broker/DBM Transport Pending Msg Frags for Send I/O

 

Pure Guesswork

This final list of counters is guesswork, as all values were zero across all servers for the polling interval. In lieu of any data to the contrary, I would consider these to be realtime counters.

SQLServer:Broker Statistics Forwarded Pending Msg Bytes
SQLServer:Broker Statistics Forwarded Pending Msg Count
SQLServer:Broker/DBM Transport Pending Msg Frags for Recv I/O
SQLServer:Broker/DBM Transport Recv I/O Buffer Copies Count
SQLServer:Database Mirroring Log Remaining for Undo KB
SQLServer:Database Mirroring Log Scanned for Undo KB
SQLServer:Databases Commit table entries
SQLServer:Exec Statistics DTC calls
SQLServer:General Statistics Event Notifications Delayed Drop
SQLServer:General Statistics Trace Event Notification Queue
SQLServer:Memory Manager Memory Grants Pending
SQLServer:Resource Pool Stats Pending memory grants count
SQLServer:Transactions Update Snapshot Transactions
SQLServer:Wait Statistics Memory grant queue waits
SQLServer:Wait Statistics Transaction ownership waits
SQLServer:Wait Statistics Workspace synchronization waits
SQLServer:Workload Group Stats Active parallel threads
SQLServer:Workload Group Stats Blocked tasks
SQLServer:Workload Group Stats Max request cpu time (ms)
SQLServer:Workload Group Stats Queued requests

 

Confirmed

Of the 140 realtime counters I captured, the remaining 94 have all been confirmed to be “truly realtime”. I will not list them here, but they are all counters of type 65792 that are not in one of the lists above. They were confirmed to be realtime due to their values bouncing up and down multiple times during the polling interval across all servers.

 

When I started this project to build a data warehouse of performance counters, I never thought this would get so messy. Hopefully this series of posts helps someone else out there trying to figure out the same problem as I’ve been doing.

Feedback, as always, is more than welcome!

Advertisements

From → PerfCounter DMV

Comments are closed.

%d bloggers like this: