Of the thousands of mails I receive every day about SQL Server problems, I was recently pinged by a friend who reported a weird problem. He started with a simple question. He said that he wants to monitor SQL Server Performance counters for complete day to send a report back to his manager. That was a simple one and I asked to capture performance counter data of SQL Server using performance monitor tool (PerfMon.exe). I thought the solution was done and was about to close the chat window that I was questioned for the second time. This was tough one I thought – “I am not seeing any performance counter for my instance”.
I asked him to send a screenshot and instance details. Here is how his perfmon counters screen looks like. (Start > Run > Perfmon.exe) and then Right Click “Add Counters…”
Since we are dealing with a default instance of SQL Server, we should see “SQL Server:Access Methods” as the first counters (they are alphabetical). This got me curious and I wanted to really understand why this could ever happen. I politely asked my friend if he was ready to do some sort of screen sharing at a later date.
I looked at the SQL Server ERRORLOG file first and there was nothing interesting under that. I asked to query sys.dm_os_performance_counters to check if we have counter values there. Here is the screenshot.
We can see that counters are available in SQL Server Engine but not shown in performance monitor tool.
Asked to look into below key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance
We were not seeing many registry keys as compared to my system.
Knowing something has gone wrong terribly, the only option left at this point was to reload the counters. Here are the commands.
To unload counter
Default Instance: unlodctr MSSQLSERVER
Named Instance: unlodctr MSSQL$<InstanceName>
To load the counter we can look at the same key and look at the value of “PerfIniFile” which is “perf-MSSQLSERVERsqlctr. ini” in the above screenshot. The file is located under BINN folder. For my machine, it is “E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn”
To load counter
Default Instance:
lodctr “E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\perf-MSSQLSERVERsqlctr.ini”
For named instance, we need to check the file and path and run below (my machine has named instance of SQL Server 2014 called SQL2014)
lodctr “E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Binn\perf-MSSQL$SQL2014sqlctr.ini”
Once that is done, we should be able to see the counters (shown below)
A big sigh of relief as this was a great learning and sharing time for me and I was able to help my friend. I am sure this was helpful to you too, if you ever encounter this situation. Do let me know.
Reference: Pinal Dave (https://blog.sqlauthority.com)
21 Comments. Leave new
This happened on our SQL 2014 test server and your steps solved the issue. Does anyone know why this would happen? In our case the counters were there one day and gone the next. Very strange!
Not sure why it can go missing, but have seen reloading solved the problem for most people. Glad this was of some help.
Hi Sharon,
On my Server that happened after the Reporting Service configuration. I tested loads of times, every time it was due to Reporting Service. Don’t know why though.
I have this issue on a 2014 SQL Server running on a Windows 2012 VM. I ran through the steps but the counters did not come back. I’ll post back if I find the solution. Thanks for all the help Pinal!!
I should mention that when querying the DMV there were no results in SQL Server.
I have the same issues as you, running SQL Server 2014 on a Windows 2012 VM. Did you ever find a solution?
Also seeing this issue on a 2008R2 Production server where I previously could see the Counters. I ran the command, but reloading perfmon does not result in finding the counters. I’ve not rebooted.
Hi
I’ve just had the same problem (Win2012R2 DC, SQL2014EE, Azure VMs). Doing scripted / automated installs of SQL Server – one machine out of the three built that day didn’t have the counters.
Took a combination of things to get it going again – including, unfortunately, a reboot. Fortunately the machine is not (yet) in a production environment.
I am having similar issue with SQL 2014 where multiple instance are installed and server is in production.. Tried all the above with no luck… I may need to open the MS case to resolve this issue. Will let you all know if MS found the resolution..
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/help/2973444/fix-sql-server-performance-counters-are-disabled-when-you-move-the-sql
Hope this helps other people who are having same issue.
Hi,
I was also in similar situation on SQL 2012. I have solved the issue with the following procedures.
First, I rebuilt the operating system counters by using
C:\windows\system32>lodctr/r
Then I unloaded the remaining SQL server counters
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>unlodctr MSSQLSERVER
Finally, rebuilt the SQL server counter
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn> lodctr perf-mssqlserversqlctr.ini
Hope it helps.
Thanx for this post! It works for me.
It worked for me too! Thank you for your help!
Hello Pinal,
I have brain burn from reading about this so much, but my situation appears to be sort of unique and I have found nothing that has worked to this point. I have a four node cluster (WS 2012 R2, SS 2014 Ent). Two of the nodes have the counters and two of them do not. I have gotten to the point where I believe it is an installation corruption because the registry keys (shown above specifically being the Open, Close, Library, and connect) that are part of the SQL installation are missing and therefore just doing a counter reload does not work. I confirmed through checking the install logs of these node historically that the counters were there at a point in time and then just went away.
I am therefore trying to do a repair, which I am attempting to do by moving the active instance to another node and then running the repair on the now offline instance. The repair fails from what I can tell due to an attempt to change components on the ACTIVE instance, which is on another node at this point. My only guess is that the repair has to be done on the active instance.
Can you confirm whether one or both of the following would have to be done?
A) Move the Active instance to one of the affected nodes and do the repair at that time (rinse and repeat on each corrupt node), or
B) Remove the affected nodes from the cluster and re-add them (effectively reinstalling the software as part of the prepare action)
I am going to be doing one or both of these actions this week, but would sure like some assurance as to whether I am going down the correct path.
Thanks, and nice talking with you again.
Bryan
Dear Pinal,
Thanks for this post – this has helped a lot with a problem which is not well documented. This happened to be as I initially installed SQL Server 2014 Standard.
One improvement is that the screen shot of where to find the counters was different in my case, since they are named MSSQL$INSTANCE- and not SQL Server- so I was looking in the wrong place and thought it had not worked.
Roger
Thank you for your kind suggestion.
I totally totally agree with your note.
Hi Pinal,
Thanks for the article, does this also work the very same for SSAS ? The message i receive is:
Unable to open driver SYSTEM\CurrentControlSet\Services\MSOLAP$MSSQLSERVER. Status: 2
What is E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\perf-MSSQLSERVERsqlctr.ini is missing as well ?Can you just copy it from a different server with same base version and put it in this server and run the commands? Thx
I can take this issue one step further and confirm that my HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance folder doesn’t even exist on my registry. Which is making me think the actual installation had issues that we never pinpointed. It’s also making it impossible for me to rebuild anything or even find the .ini file recommended above because I don’t think it has ever existed. Do you or anyone here know of a way to build the Performance folder in the registry after the instance is already live?
Have you resolve this issue? I am having the exact same problem
I hace Windows 2008 R2 with SQL Server 2012 Express.
I followed all steps but SQL Performance metrics dont appear in perform.exe (32 or 64 bits).
I have a brain burn too!
Please help.