Consuming Data from the PerfMon Counters

SQL Server MVP Kent Tegels recently raise an interesting question. SQL Server publishes a number of performance counters. In the code he's written that publishes performance counter data, he's had to explictly code the counter updates within his code regardless of knowing if any processes were consuming those counters. Kent brought up some interesting thoughts regarding such a coding situation.  Have you ever wondered:

a.) Is this true for SQL Server, so does it only update counters that it knows are being consumed?

b.) If a is true, then, short of the resources consumed by the PerfMon process itself, is there any other impact to SQL Server of monitoring its counters?

Peter Byrne, from Microsoft, pointed out that the PerfMon counters are updated regardless of whether anyone is collecting them. There is a mechanism in the API that you can use to find out if anyone is looking at any of the counters overall, but PerfMon doesn't allow you to filter by a specific counter/object.  So that makes it pretty hard to update only monitored counters, because it basically cannot tell which counters are being monitored.

Furthermore, the performance impact of the counters is typically only going to show up when a system is run at its maximum possible performance, such as in a benchmark test. The actual level of impact tends to between from hardware and OS platforms and between SQL releases, because the team constantly identifies new bottlenecks and address them.  Interestingly, Peter has even seen platforms where enabling the counters helped  performance by a couple of percent. You can use the -x startup flag to test the impact of PerfMon counters on your application.

Also remember that SQL Profiler events do have a filtering mechanism.  So their impact can be better administrated than PerfMon because they are only fired if someone is listening for the specific event.

Enjoy!

-Kevin