Partager via

SQL Performance Counters are Missing

  • Article

There was one time when I was delivering a Service using a tool that gathers performance data through the SQL Server Counters on Performance Monitor, I got surprised when I tried to review the data and there was no information. Then I checked the SQL Server counters directly on Performance Monitor and the counters were not on the list.  That's why I decided to write this blog, to know what to do if the SQL Server counters on Performance Monitor disappears.

The right and easiest way to recover the SQL counters is to repair the SQL Server binaries, through add/remove programs or SQL Installation media.  However this will require downtime on the SQL instance (if you're using a cluster you could do a failover to another node). Also please Install the latest Service Packs and Cumulative updates in your SQL Server as this may cause this situation as well. 

But if this fails, there's another option, you could recover this counter executing the following steps:

First you need to open a command prompt console with administrative rights.

Go to the directory BINN on the SQL Instance you want to fix (Ex: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn)

Now to avoid any inconsistency eliminate any leftovers of the SQL Counters using unlodctr MSSQLSERVER, for named instances use the format MSSQL$InstanceName

Now to load the counters you should use the following command lodctr perf-MSSQLSERVERsqlctr.ini, if you're using a named instance you should use this format perf- MSSQL$InstanceNameqlctr.ini, you could execute the command twice to check that the counters have been added.

Now you should restart the services Remote Registry and Performance Logs & Alerts, using the followings commands:

net stop "Remote Registry" & net start "Remote Registry"

net stop "Performance Logs & Alerts" & net start " Performance Logs & Alerts "

If you are using the previous procedure to recover the SQL counters on a SQL Server Cluster, you should rebuild the counters on both nodes, when you are rebuilding the counters the SQL Instance must be active on the node.

If after you have executed the above steps the SQL Counters have not appear, you will need to review the registry hives and keys (it's not recommended to do any changes at the registry level, doing so could lead to instability on the operating system.  If you chose to do these changes you should have a backup of the Operating System and Registry, to recover it if necessary), the following registry keys should exist and have a valid configuration.

[HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\<<service name>>\Performance]

Library
Open
Collect
Close
PerfIniFile

You should also check if there's a key named Disable Performance Counters inside HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib, if it is configure with the value 1, it should be configure with 0. This should allow you to rebuild the registry counters and help files. 

Now it may or not be necessary to rebuild the SQL and/or Operating System counters, look for the SQL Counters on perfmon, if they aren't there use the previous steps to rebuild then, with the following command you can rebuild all the operating system counters.

cd %systemroot%\system32

lodctr /R

Another consideration that you should have is when you are executing SQL Server 32 bits on an Windows Server 64 bits environment

I hope this post is helpful, remember there's a ton of good post on our blog that can help you on many SQL Server topics and how to Troubleshoot some problems. See you on the next post!

Comments