Ryan Adams Blog

Microsoft Data Platform

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.

Performance, SQL Server, SQLServerCentral Syndication, SQLServerPedia Syndication - by Ryan Adams

8 thoughts on “Reinstall SQL Server Performance Counters”

  1. Yeah, I used to have this issue all the time and then not for several yrs. Then last yr I started seeing it again. Many times though I find that this simplest of fixes doesn’t do the trick. There are other methods I won’t outline here, but they’re easily discoverable on the web.
    This method is certainly valid, it just hasn’t worked for me very often.

  2. Thanks Sean! So far so good for me on quite a few boxes, but I guess we can say buyer beware.

  3. Thanks for the tip, Ryan. I likewise had not seen this problem in quite a while until recently it popped up again. I’m thinking PowerShell script could easily do this! But, then we’d probably run into the same problems Sean referenced and I’d have to go to plan B! Something for me to think about!

  7. Can this be done on a live server with no interruption? I assume it only affects the Perfmon counters and not the SQL operation itself. It appears so to me, but I want to be sure!

Comments are closed.