This problem is a real pain and I’m yet to determine why this issue occurs because my understanding is SQL Server counters are automatically installed as part of SQL Server installation. Let’s pretend you have recently installed SQL Server 2008 R2 instance on one of our company server. After the installation you noticed that SQL Server 2008 counters are not installed as part of SQL Server 2008 installation.
Now if you ever encountered this problem with SQL Server installation then by using an elevated administrator command prompt perform the following steps to re-register sqlctr100.dll, and run the file sqlctr.ini located in the binn directory for the instance:
- Change the path to the BINN directory of the SQL Server instance you desire to correct. (Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn)
- Execute unlodctr <<REGISTERED SERVER NAME>> For example: unlodctr MSSQL$SQLDev2008 for SQL Server with name instance (SQLDev2008) or unlodctr SQLAgent$SQLDev2008 for SQL Server Agent, …
- Execute lodctr :<<perf-sql* matching the counters you desire to load>>, For example: lodctr perf-MSSQL$SQLDev2008sqlctr.ini for SQL Server and lodctr perf-SQLAgent$SQLDev2008sqlagtctr.ini for SQL Server Agent, … The /T is important to load the SQL Server performance counter provider as a trusted provider.
- Cycle the remote registry service net stop “Remote Registry” and net start “Remote Registry”.
- Force a WMI synchronization using winmgmt /resyncperfctr “<<PID>>” where PID is the process id of the WinPriv.exe (you can get this from Task Manager)
Now you can see the performance counter for that instance in PerfMon.