Missing SQL Performance Counters

If you have been using performance monitor (perfmon) counter or trying to setup up one to monitor your SQL Server performance, and come to find out that there are no SQL Server Perfmon counters on the list, hopefully this blog post can help in your troubleshooting.

Before running commands below or applying any registry change, do a quick check on this registry key to verify if the performance counters have been explicitly disabled (set to 1) for entire system,

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\Disable Performance Counters

Or on the particular SQL instance,

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance\Disable Performance Counters

Simply changing this registry key to zero enable the performance counter back. If this counter does not exist, performance counter is enabled by default.

If this is not the cause, verify if the SQL counters appear on the local Perfmon. If they appear on the local Perfmon, verify the remote registry service is started.

Run services.msc and in the services window, scroll down until you see remote registry. Verify it is started.

I have incidents that issuing Get-Counter PowerShell command cause the remote registry to terminate. See below for more descriptions.

 If it doesn't. you could try using unlodctr and lodctr to re-register the SQL counters.

1) Open an elevated command prompt (run as administrator).

2) Navigate to the SQL instance binn folder (the SQL instance that is missing the perfmon counter)

e.g. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn

3) Use unlodctr to unload SQL counters
e.g. unlodctr MSSQLSERVER (for default instance)
e.g. unlodctr SQLSERVERAGENT (for default SQL Agent)
e.g. unlodctr MSSQL$TEST (for named instance)
e.g. unlodctr SQLAGENT$TEST (for SQL agent)

4) Load and register SQL counters with lodctr
e.g. lodctr perf-MSSQLSERVERsqlctr.ini (for default instance)
e.g. lodctr perf-SQLSERVERAGENTsqlagtctr.ini (for default SQL Agent)
e.g. lodctr perf-MSSQL$TESTsqlctr.ini (for named instance)
e.g. lodctr perf-SQLAGENT$TESTsqlagtctr.ini (for SQL Agent)

5) Restart remote registry services.
net stop "Remote Registry" 
net start "Remote Registry"

6) If necessary, re-synchronize WMI with associate WinPrivSE.exe process ID (PID). The PID can be found from task manager.

e.g. winmgmt /resyncperfctr "5660"

Note: Replace 5660 with your own PID.

A lot of times we run Perfmon on different server to extract and store the perfmon data of the remote SQL server. I have a few incidents that if I ran the Get-Counter PowerShell command as the first connection to the remote server (after server restart or applying the above steps), I received this error 'Unable to connect to machine' later when trying to connect to remote server with Perfmon. (In this case, it is not access permissions issue)

The remote registry service at the remote server appears to have terminated by itself with a system error. Here is the error from event viewer,

However, if I use Windows Performance Monitor tool (Perfmon/Logman) as first connection, it does not has the problem. In my case, both monitoring server and remote server are Windows Server 2008 R2 and the PowerShell is Version 3. This doesn't appear to be the same problem as described in this Microsoft Support KB, but somehow has similar behavior. I haven't thoroughly investigate and reproduce the error, so you environment may have different behavior.

If all steps above fail to register the SQL counters, you could consider rebuilding the entire performance counter registry.

lodctr /R

Warning: Please be aware that this command overwrite all performance counter registry setting and replace with configuration defined in the file specified.

For more thorough investigation and troubleshooting, refer to Bob Dorr's blog post on troubleshooting performance counter collection problem.