Freigeben über

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

  • Artikel

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.

Comments

  • Anonymous
    January 10, 2008
    This issue is now fixed. The fix should be available in the SQL Server 2008 CTP7. Thanks, -Ivan

  • Anonymous
    February 05, 2008
    In our case its showing values when monitored through Perfmon, but showing values as Zero through Sitescope. Please help..

  • Anonymous
    February 06, 2008
    Hi Neetha, Thanks for reading my blog and thanks for leaving your comments here. Unfortunately, SiteScope is not a Microsoft product and I don't even have one installed as to try to determine which mechanism it is using to consume SQL Server performance counters, so I cannot provide you with much help in this case. But I strongly suggest you to contact HP support (Mercury Interactive, who wrote SiteScope, was acquired by HP somewhere in 2006). I'm pretty sure HP support will be happy to help you determining the root cause of this issue you expose here. Thanks, Nacho

  • Anonymous
    February 06, 2008
    It seems Cumulative Update 6 (due in mid Feb 08) for SQL Server 2005 Service Pack 2 will include a code

  • Anonymous
    February 06, 2008
    It seems Cumulative Update 6 (due in mid Feb 08) for SQL Server 2005 Service Pack 2 will include a code