다음을 통해 공유

Registry Settings of the SQL Server Performance Monitor Objects

  • 아티클

You may sometime find that the SQL counters in the Performance Monitor are missing, this happens due to the restart of the SQL server while it is running or, due to a cluster failover in the server.

1 Registry Properties of Perfmon

The registry properties of a perfmon can be observed at the registry location HKEY_LOCALMACHINE\SOFTWARE\MICROSOFT\Windows NT\CurrentVersion\Perflib

 


                                                                        Fig 1.

There you can see the values of LastCounter,Last Help and it has come specific values.

 

If you go into the next sub directory of 009 (which indicated language as English) and  look into the Counter tab you will observe like below Fig 2.

 

                                                            Fig 2.

So from here you can clearly observe that every performance counter  will have a specific digit assigned to it. Note down the last value and if it doesn’t match with the Lastcounter value in the directory level high (shown in the fig 1). It indicates a problem. And you can copy the entire data in the 009\Counter (fig 2) value in a text file and you can search for the counters of the SQL instance are present or not. If they are not present  we need to rebuild the SQL counters again by the process explained in the coming sections.

2 Registry Settings of SQL Service

Registry settings of SQL service will look like below

 

                                                            Fig 3.

The registry settings of the SQL Service are present in the CurrentControlSet\Services\ section.

Here if we go into the SQL Service performance directory and you can observe the Screen shot as above.

Here the last counter and First Counter must match with the data that has been collected in Fig 2. If these don’t match the Performance Monitor will not be able to relate the Counters with the SQL service.

Whenever a SQL gets installed a configuration file SQLCTR.ini is generated (if you are using a named instance a new file with the name perf-<instancename>sqlctr.ini is generated in the Binn folder of the SQL service directory. The SQLCtr.ini is always accompanied with SQLCTr.h file which is the help file and contain the complete information and description regarding the SQL counters.

And the data in the registry values of Close,Collect,Open,PerfInFile,Library should be present for the SQL to populate the counter values. If these are also not present we need to rebuild the SQL counters once again.

 

 

 

3 What Should the SQLCTR.ini File Look Like

 

The SQLCTR.ini that we load for getting the Perfomance Counters of the SQL should look like below.  If it is a default instance in place of a named instance then our SQLCTR.ini looks the same but at the place of each MSSQL$<instancename> we will have SQLServer in the place.

So if the naming convention is not proper and if have any descripancies in the naming convention even though we load the SQLCTR.ini the counters won’t show up in the perfmon.

 

[info]

drivername=MSSQL$<instancename>

trusted=

symbolfile=<Location of the Help File>

 

 

[languages]

009=English

 

 

[text]

BUFMGR_OBJECT_009_NAME=MSSQL$<instancename>:Buffer Manager

BUFMGR_OBJECT_009_HELP=Statistics related to SQL Servers buffer manager

 

 

BUFPART_OBJECT_009_NAME=MSSQL$<instancename>:Buffer Partition

BUFPART_OBJECT_009_HELP=Statistics related to SQL Server's buffer partitions

 

 

BUFNODE_OBJECT_009_NAME=MSSQL$<instancename>:Buffer Node

BUFNODE_OBJECT_009_HELP=Statistics related to SQL Server's buffer pool by NUMA node

…

..

..

 

 

 

4 Rebuilding SQL Counters

 

So elaborating  these are the steps  which we need to follow

 

  1. Go to the path Program Files\Microsoft SQL Server\instance name>\MSSQL\Binn from the command prompt

**b.   ** Execute unlodctr MSSQL$<instancename>

  1.  You can execute it twice for getting a confirmation whether the counters are really unloaded

**d.   ** Execute lodctr perf-<instancename>sqlctr.ini (for named instance) or lodctr sqlctr.ini (for default unnamed instance).

Here also you can execute the command twice for getting the confirmation

  1. Cross verify the registry keys (explained in Section 1,2) –we should perform the Step because we need to confirm as we can’t afford SQL restart twice ina production environment.
  2. Restart SQL service.
  3. Open CMD and  execute net stop “Remote Registry” and net start “Remote Registry”.