Why my SQL Server performance counters are showing zeroes when current values are higher?

  • Article

A colleague reported to me what she considered a weird behavior, she was experiencing while trying to monitor her SQL Server 2005 instances' performance counters.

Her main concern was this:

Why do my SQL Server 2005 counters do not report actual values, if I have started PerfMon and added some counters while SQL wasn't running, even though SQL Server is currently running? If, for example, I open an instance of Performance Monitor and, while my default instance of SQL Server 2005 is stopped, I add the "User Connections" counter which is part of the "SQLServer:General Statistics" object, then even if I start the instance of SQL Server and establish several connections against it, that counter continues showing 0 connections. While if I add the performance counter while the service is running, no matter how many times I stop and restart the service, it will always show actual values for the counter (ie. 0 when the instance is stopped, 0 when the instance is running and no client is connected, or higher values representing the current amount of users connected to SQL).

I reviewed our implementation of sqlctr90.dll and found that if, when our implementation of OpenPerformanceData is called (sqlctr90!SQLServerInstance::FOpenSQLInstance), if the SQL Server service is not running, then we set up ourselves in a mode we call static mode, which will only expose our performance objects and counters but no actual data will be retrieved at collection time, when our implementation of CollectPerformanceData is called. Under those circumstances, we set the zeroes for the performance counters values (sqlctr90!CollectCounters). So, unless you call our implementation of OpenPerformanceData again, and PerfMon doesn't do that unless you launch a new instance of PerfMon and enumerate the performance objects (by trying to add new counters).

I don't really know the reasons behind this design, but just wanted to clarify it's intentionally coded to work that way. I have opened a Work Item, so that the product team evaluates the possibility to make this experience more consistent, independently of the status of the SQL service at performance objects enumeration time.

Hope it helps you understanding how and why it works that way.