As a SQL Server administrator, it is not uncommon to come across various issues and problems related to performance monitoring. Recently, I received a query from a friend who was facing a peculiar problem with SQL Server performance counters. He wanted to monitor the performance counters for an entire day and send a report to his manager, but he couldn’t find any performance counters for his SQL Server instance.

Initially, I suggested using the Performance Monitor tool (PerfMon.exe) to capture the performance counter data. However, my friend informed me that he had already tried that and couldn’t find any counters for his instance. This piqued my curiosity, and I decided to investigate further.

First, I asked my friend to send me a screenshot of his PerfMon counters screen. Upon examining the screenshot, I noticed that the “SQL Server:Access Methods” counters, which should be present for a default instance of SQL Server, were missing. This was indeed a strange situation.

To troubleshoot the issue, I asked my friend to query the sys.dm_os_performance_counters view in SQL Server. Surprisingly, the counters were available in the SQL Server Engine, but they were not being displayed in the PerfMon tool.

Next, I asked my friend to check the registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance. Comparing it to my own system, we discovered that there were fewer registry keys on his machine. It was evident that something had gone wrong, and we needed to reload the counters.

To unload the counters, we used the following commands:

unlodctr MSSQLSERVER (for the default instance)

unlodctr MSSQL$<InstanceName> (for named instances)

To load the counters, we looked at the value of the “PerfIniFile” key in the same registry location. The value, in this case, was “perf-MSSQLSERVERsqlctr.ini”. The corresponding file was located in the BINN folder of the SQL Server installation directory.

For the default instance, we used the following command to load the counters:

lodctr "E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\perf-MSSQLSERVERsqlctr.ini"

For named instances, we checked the file and path and ran a similar command, replacing the instance name accordingly.

lodctr "E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Binn\perf-MSSQL$SQL2014sqlctr.ini"

After executing these commands, we were able to see the missing counters in the PerfMon tool.

This experience was a great learning opportunity for me, and I was glad that I could help my friend resolve the issue. If you ever encounter a similar situation where SQL Server performance counters are missing, I hope this guide proves helpful to you as well.

Feel free to reach out to me if you have any questions or need further assistance.