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 (17)

  1. Anonymous says:

    Thanks for you Coments!

    You could do a SQL Repair if possible.If not a copy/paste should work fine. Cheers!

  2. Anonymous says:

    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.

  3. SQL PFE LATAM says:

    Thanks for your comment Mahmoud!!

  4. Stephen Barash says:

    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…

  5. Sangameshms@live.com says:

    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.

  6. joeri.lansbergen@live.com says:

    thanks it works!

  7. Rakesh Baro says:

    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.

  8. Israel Gomez says:

    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.

  9. David Ruiz de Olano says:

    Hi Edison,
    When you say "you should rebuild the clusters on both nodes" you mean "rebuild the counters", don´t you?
    If each instance is running on its node, could I execute the command for both instances on both nodes, executing it on the instance´s directory?
    When I restart "Remote Registry" and "Performance Logs & Alerts", does it affect to the SQL Server service?. I mean, is it going to suffer any interruption? I suppose it won´t.
    Thank you very much.

  10. 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!

  11. David Ruiz de Olano says:

    Thank you very much Edison!!

  12. Jason JIang says:

    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.

  13. Dean says:

    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.

  14. Thanks. This work for me.

  15. S.E. says:

    It worked, thanks. In my case, restarting the 2 mentioned services was not even necessary.

  16. Santosh says:

    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/2973444

    Hope this helps other people who are having same issue.

Skip to main content