If we have x64 Windows and X86 SQL.
In this case, for looking into X86 instances of SQL, we need to use the 32 bit equivalent of Performance Monitor. We can find that in C:\Windows\SysWOW64 or open that from RUN Command by typing the following.
perfmon /32
Now how we do we load the Performance Objects and counters back (in case they are not present. I guess that’s not present and that’s what led you to this post in the first place.)
1) First unload the counters, to remove any remnants. We use the unlodctr component(shipped with Windows) to do that.
We need to run the following from the Run command.
unlodctr mssql$<instance name>
--> If we are dealing with a default instance, then we need to run
unlodctr mssqlserver
If it is not loaded, we will get a well self explanatory message.
2) Then we need to identify the proper INI file using which we would load the counters. The actual path for the counter can be located in the following location
HKLM\SYSTEM\Services\Currentcontrolset\Services\MSSQL$<instance name>\Performance\
The Key in this Location would be PerfIniFile
(For default instance HKLM\SYSTEM\Services\Currentcontrolset\Services\MSSQLServer\Performance\PerfIniFile)
For Named Instance the file name would be in the format perf-<instancename>sqlctr.ini
For Default instance it would be in the form of sqlctr.ini
The file is present in the Binn Directory for the respective instance. Using the Windows Desktop Search facility we might save ourselves some time, if we are not sure where to find the specific file.
3) Now use the lodctr utility (from RUN Command) to load the Objects and counters using the identified INI file from above step. We need to specify the complete path for the INI file as the only option for lodctr.
lodctr <complete path for the ini file identified from the above steps)
On a clustered instance the unlodctr and lodctr works the same way, its only that we might have to failover the SQL to each node and run the same steps on each node.
That's all :)
Note: There may be some other causes as well but here I presented the most typical cause of disappearing SQL performance counters and the steps how to restore them.
For further details please refer to the links below:
