Reinstall SQL Server Performance Counters

I’ve had several instances over the last few months where I had to reinstall the SQL Server Performance Counters.  It sounds odd to have them magically disappear, but I’ve found a 3rd party monitoring product to be the culprit.

First we need to unload the counters just to make sure everything is removed and we are starting from a clean slate.  It’s better to be safe than sorry.  Open a command prompt as an Administrator and choose one of the following:

Default Instance

unlodctr MSSQLSERVER

Named Instance

unlodctr MSSQL$MyInstanceName

Now we need to re-load the counters.  They are stored in an INI file.  The file is located in the binn directory of the installation folder.  You can get the name of the file from the following registry key:

Default Instance

HKLM\SYSTEM\Currentcontrolset\Services\MSSQLServer\Performance\PerfIniFile

Named Instance

HKLM\SYSTEM\Currentcontrolset\Services\MSSQL$MyInstanceName\Performance\PerfIniFile

Now that we have the file name we just need to run the load counter command from an elevated command prompt.  Here is what the command would look like for a default instance of SQL Server 2012.

lodctr “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BINN\perf-MSSQLSERVERsqlctr.ini”

Hopefully this helps if it ever happens to you.

UPDATE (7/2/2015):  I had this happen again today and was referencing this article when I realized I left something out.  After you have reloaded the counters you should restart the SQL instance.  Sometimes you might also need to restart the remote registry service as well.