Q & A: Restore Missing SQL Server Performance Counters

Q: My SQL Server was having some performance issues and to attempt to get a handle on the nature of the problem I was going to run Perfmon to collect some performance metrics. However, when I ran Perfmon I saw that there were no SQL Server performance counters. There were some counters for SQL Agent but nothing for SQL Server. How do I restore my SQL Server performance counters?

A: Microsoft provides a couple of procedures that you can use to unload and load the SQL Server performance counters into Perfmon. If your SQL Server performance counters are missing first open an elevated command prompt then go to the BINN directory of the SQL Server instance you want to repair. For SQL Server 2014 you can use the following command:

cd c:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBinn

Next, run the unlodctr command to cleanup any possible problems that might exist with the SQL Server performance counters. To repair the default instance use the following command. To repair a named instance substitute MSSQL$ plus the instance name for MSSQLSERVER.

unlodctr MSSQLSERVER

To reload the counters to the default SQL Server instance you need use the following command. To reload them to a named instance substitute the instance name for MSSQLSERVER.

lodctr perf-MSSQLSERVERsqlctr.ini

Finally, you need to stop and restart the Remote Registry and Performance Logs & Alerts services. You can use the following commands to stop and restart the services.

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

When you reopen Perfmon your SQL Server performance counters should be restored.