SQL Performance Counters are Missing

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 were no information. Them I checked the SQL Server counter 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, trough add/remove programs. However this will require downtime on the SQL instance (If you’re using a cluster you could do a switch-over to another node)

Therefore 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!

 

“The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft”

Comments

• Anonymous
  January 01, 2003
  Thanks for you Coments!
  
  You could do a SQL Repair if possible.If not a copy/paste should work fine. Cheers!

• Anonymous
  January 01, 2003
  I found this useful for the following issue: The configuration information of the performance library "perf-MSSQLSERVER-sqlctr11.2.5058.0.dll" for the "MSSQLSERVER" service does not match the trusted performance library information stored in the registry. The functions in this library will not be treated as trusted.
  What I found strange was that neither the Remote Registry service or the Performance Logs and Alerts service were started on this server. I am a SQL DBA not a windows admin so........... Thanks for the information though, worked well.
  

• Anonymous
  November 18, 2013
  Great ..

• Anonymous
  November 25, 2013
  Thanks for your comment Mahmoud!!

• Anonymous
  February 20, 2014
  All I was seeing was MSIS & Agent counters with SQL 2012. Tried all of the above without luck. However starting the Performance Counter DLL Host service resolved the issue and now all the SQL counters are there...

• Anonymous
  March 14, 2014
  Hi Team,
  
  In my case the file perf-sqlctr.ini. is missing from Bin directory in passive node, hence I can't able to reload performance counter registry settings using lodctr, I found the same file in another (active) node in BIN directory and I am not sure how do I restore back the perf-sqlctr.ini. file on Bin directory of MSSQL Server installation on passive node. Then I can repair it using lodctr,
  
  Could you please anybody can assist on this.
  

• Anonymous
  August 28, 2014
  thanks it works!
  

• Anonymous
  December 01, 2014
  I would like to know why this counters get disabled in the first place. It would be a great help if we know the root cause.

• Anonymous
  June 05, 2015
  Thanks for the article! It helped rebuild my SQL Agent counters, however the SQL Server performance counters are still missing. Following Stephen Barash's comment from Feburary 2014 I started the "Performance Counter DLL Host" service with zero luck. Anyone one have any suggestions? This is occurring on a 2014 & 2008R2 SQL Server.

• Anonymous
  June 30, 2015
  The comment has been removed

• Anonymous
  June 30, 2015
  Hi All, its been sometime since I review this post.
  
  I'm glad it have help most of you.
  
  @Israel, I might need some more details around your issue.
  
  @David, Yes counters (Fixed), Yes that's correct, No interruption for the SQL Service.
  
  Cheers!

• Anonymous
  June 30, 2015
  Thank you very much Edison!!

• Anonymous
  November 17, 2015
  Hi all,
  
  thanks for this great post. I followed this guide to rebuild my SQL server performance counter sucessfully but after every restart, they are missing again. Do you know why and how to avoid this issue?
  
  Thanks for your comments.
  

• Anonymous
  November 30, 2015
  I have experienced the same issue, counters appear to randomly stop. I am sure this is a bug in SQL Server, and I've not seen anywhere where the root cause has been identified.

• Anonymous
  December 20, 2015
  Thanks. This work for me.

• Anonymous
  February 18, 2016
  It worked, thanks. In my case, restarting the 2 mentioned services was not even necessary.

• Anonymous
  April 05, 2016
  I also had the similar issue on SQL 2014 and did all the recommendation above, however issue was not resolve. Upon raising MS case, they suggested to install CU2 update on instance and that has resolve the issue.https://support.microsoft.com/en-us/kb/2973444Hope this helps other people who are having same issue.