In yesterday’s post, we had discussed how to go about uploading the perfmon data into a database for querying the data using simple SQL scripts. In this post, we will cover the scenarios when you see that a lot of your performance counters are missing. This question was asked by one of our readers and is also a very common situation in many different shops. We have seen this happen when there are clustered servers or when counters get updated by a hot fix or a service pack.
If you look into the Windows resource kit, you will find a utility called CTRLIST.exe. This reports on all the objects and the counters that are loaded on a particular server. There is also a GUI version of the utility available and the reason why we would recommend using either the command line version or the GUI version is to be able to look at the specifics of the DLLs that are related to each of those counters. That way, it becomes easier to troubleshoot in case a counter is missing or is not working for some reason. You can download the Windows Resource Kit from here.
After reviewing the output from the GUI utility or the text file from the command line utility, you can then check the Windows Application and System logs to see if there are any warnings or errors related to the loading of the performance library. If nothing specific is noticed, we would recommend just unloading and reloading the counter, example:
If the SQL Server counters are missing in action:
a) From the output from the GUI/text file from the command line utility, make a note of the sqlctr.ini file location.
b) Unload the SQL Server counters by executing:
c) Reload the counters:
Lodctr C:\Program Files\SQL\MSSQL.1\MSSQL\Binn\sqlctr.ini
If you are using a clustered instance, then you need to use the Virtual Server Name in the unload and reload commands and if you have a named instance, then you need to use the virtual server name. If that also does not work for you, look up this KB article from MSFT that talks about rebuilding the entire performance counter library. Please do all this on a test system first to become comfortable with the steps.