Recently I was working on one of the servers to analyze the performance using Performance Counters which were collected historically for a long period of time.
I noticed some of the needed counters were missing for the collection.
So what was happening with that server where performance counters were missing? After running the following query on current snapshot of performance counters, I did not get the result. SQL Server was not reporting any counters.
After doing some research I found that there is one configuration file that lists all the performance counters which SQL Server loads. The name of the performance counter configuration file is generally perfsqlctr.ini or perf-sqlctr.ini located in Binn directory.
To precisely get the file name and location, you may take help from Registry.
- In Registry Go to HKLM\SYSTEM\CurrentControlSet\services\
- Extract the value from Key ImagePath until Binn
- Go to HKLM\SYSTEM\CurrentControlSet\services\[InstanceName]\Performance
- Extract the value from key PerfIniFile
- Merge the values from #2 and #4, that is the file name which has all of the counters.
Now you got all of the file names needed, follow these steps to reload the counters.
- Open file in text editor, make sure that all of the needed counters are there in the file, if not there then add counter while matching the format of existing counters.
- Unload the Counters
Restart the MSSQL Service for that instance
Reload the counters with following command from shell.
- If Default Instance then run following command from shell.
- If Named Instance then run the following command from shell.
- Unlodctr MSSQL$[ServiceName]
Restart MSSQLService for the instance
- Lodctr [path\file you got in #5]
It worked for me, I could see the counters.
If you still see the issues, then please make sure that the registry keys as discussed above are accessible by Service Account of SQL Service. Then repeat steps between 2 and 4.