PRB: SQL Performance Monitor Counters Missing

When you use the SQL Performance Monitor, some or all of the SQL counters are missing. In addition, one or more of the following error messages may be logged into the application event log:

Typically, the counters are missing due to one of the following causes:

• Problems with the initial setup of the SQL Performance Monitor.
• Problems with the login that the SQL Performance Monitor uses.
• Problems with the registry.
• Problems with the files that the SQL Performance Monitor uses.

Problems with Performance Monitor Setup

1. Make sure that SQL Performance Monitor integration is on.
   
   
   1. Run the SQL Server setup on the SQL Server.
   2. Click Continue.
   3. Click Continue.
   4. Click Set Server Options.
   5. Click Continue.
   6. Make sure that the SQL Perfmon Integration box is selected.
   7. Click Change Options.
   8. After you change the options, click Exit to return to Microsoft Windows NT.
   9. Stop and restart the SQL Server.
   NOTE: If SQL Perfmon Integration is already selected, try clearing it, changing the options, exiting setup, and then perform the preceding steps.
2. Make sure the stored procedure MS_sqlctrs_users exists in the master database. If not, use ISQL/w to run the following script (Note that the script Procsyst.sql located in the \MSSQL\Install folder also contains this code):

   if exists (select * from sysobjects where id = object_id('dbo.MS_sqlctrs_users') and sysstat & 0xf = 4)
   	   drop procedure dbo.MS_sqlctrs_users
   GO
   create procedure MS_sqlctrs_users
   as
   select syslogins.name+' - '+convert(varchar(30), sysprocesses.spid), memusage 'Memory (2K Pages)', cpu 'CPU time', physical_io, count(syslocks.spid) 'Locks held',sysprocesses.spid
   from sysprocesses, syslocks, syslogins where sysprocesses.spid *= syslocks.spid and sysprocesses.suid=syslogins.suid
   group by syslogins.name,sysprocesses.spid,memusage,cpu,physical_io
   GO
   GRANT  EXECUTE  ON dbo.MS_sqlctrs_users TO public
   GO
   					

3. Use Regedt32.exe to check the following registry key:
   
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\PerfMonSecu
   If this key exists and if the value is 0x1, Performance Monitor is being forced to make a trusted connection to SQL Server. In this case, use Enterprise Manager to make sure that the server is listening on either named pipes or multiprotocol:
   
   
   1. Run SQL Setup on the SQL Server.
   2. Click Continue.
   3. Click Continue.
   4. Choose Change Network Support.
   5. Click Continue.
   6. Make sure an X exists in the box beside either Named Pipes or Multi-Protocol.
   7. Click OK.
   8. Accept the defaults and click Continue.
   9. Click Exit to return to Windows NT after the options are changed.
   10. Stop and restart SQL Server.

Problems with the Performance Monitor Login

NOTE: The information in this login troubleshooting section does not apply when Performance Monitor makes a trusted connection to the server, which happens when:

• The server is using integrated security.
  
  -or-
• The registry has been modified so that it forces Performance Monitor to make a trusted connection. For further information on forcing the trusted connection, please see SQL Server Books Online.

1. Make sure that the probe login exists by running the following query in ISQL/w:

   sp_helplogins probe

   If the login does not exist, use ISQL/w to run the Procsyst.sql script located in the \MSSQL\Install folder to create the login with the correct permissions.
2. Make sure that the probe login has no password by performing the following steps in Enterprise Manager:
   1. Double-click probe under the logins folder.
   2. Clear the Password box.
   3. Click Modify.
   4. Click OK, without adding anything to the Confirm New Password Box.
   5. Click Close.
3. Make sure that the default database for probe is the master database by issuing the following query from ISQL/w:

   sp_defaultdb probe, master
   					

4. If the default permissions for the public group or for the probe login have changed, use ISQL/w to run the Procsyst.sql script located in the \MSSQL\Install folder to give correct permissions to the probe login.
5. Make sure that the server user ID (SUID) for the probe login matches the SUID for the probe user in the master database by issuing the following from ISQL/w:

   select sl.name, sl.suid 'syslogins suid', su.suid 'sysusers suid' from master..syslogins sl, master..sysusers su where sl.name = 'probe' and sl.suid = su.suid
   						

   If not, delete both the probe user and the probe login, then use ISQL/w to run the Procsyst.sql script located in the \MSSQL\Install folder to create the probe login and user with the correct permissions.

Problems with the Registry

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

1. Use Regedt32.exe to check the following registry key:
   
   
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\Performance\Libra
   
   
   
   1. Make sure that this key exists. If it does not, rebuild the registry by running the following in the MSSQL\BINN folder from a command prompt: setup /t RegistryRebuild = On NOTE: Microsoft recommends that you make a complete backup of your system before you perform the registry rebuild.
   2. Make sure that the value for this key is Sqlctr60.dll. If not, use the registry editor to modify the key to the correct value.
   3. Make sure that the permissions are correct on the key. In the registry editor, highlight the performance folder, and then from the top menu, click Security and then select Permissions. Make sure that the following users have the permissions indicated:
   • Administrators - Full Control
   • Everyone - Special Access
   • SYSTEM - Full Control
2. Use Regedt32.exe to check the following registry key:
   
   
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009\Count
   
   
   
   1. Make sure that this key contains all the SQL Performance Monitor counters. If not, rebuild the registry by using the preceding steps.
   2. Make sure the 009 folder has the following permissions:
      • CREATOR OWNER - Full control
      • Administrators - Full Control
      • Everyone - Read
      • SYSTEM - Full Control
3. Use Regedt32.exe to check the following registry keys:
   
   
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServ
   
   
   
   HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SecurePipeServers\winreg\AllowedPat
   
   
   Make sure that the user who is trying to monitor SQL Server with Performance Monitor has at least READ permission on these keys.

Problems with Performance Monitor Files

1. Make sure that the system path variable includes the path to the Sqlctr60.dll file.
   
   
   1. In Control Panel select System, and then click the Environment tab.
   2. Under System Variables, highlight Path.
   3. If the Value box does not contains the path to Sqlctr60.dll (by default, the path to the file is \MSSQL\BINN), add the path, preceded to the end of the string. Note that each path entered must be separated by a semi-colon (;).
   4. Click Set.
   5. Click OK.
2. If all else fails, it is possible that the Sqlctr60.dll file is the incorrect version or that it is damaged. In this case, copy the file from the SQL Server 6.5 CD. Note that this file has been changed in Service Packs 5 and 5a, so if you are running either of these two Service Packs, the file should be copied from the Service Pack instead of the CD.
3. If counters still do not show and the computer is a Windows NT 4.0-based computer, it is possible that the Windows NT performance data files, Perfc009.dat and Perfh009.dat, are damaged. In this case, they must be replaced from the Windows NT 4.0 CD by using the following steps:
   
   
   1. In the \Winnt\System32 folder, rename the following files:
      Perfc009.dat
      Perfh009.dat
      For more information, please see the following article in the Microsoft Knowledge Base:
      127207 Missing Objects and Counters in Performance Monitor
      NOTE: All files that contain "009" are used when the default language is English. The preceding file list is referring to English files. Other languages will have different numbers.
      
      
   2. Copy the files Expand.exe, Perfc009.da_, and Perfh009.da_ from the Windows NT 4.0 CD to the \Winnt\System32 folder on the local computer.
   3. From a command prompt in the \Winnt\System32 folder, expand the Perfc009.da_ file by running the following: Expand.exe Perfc009.da_ Perfc009.dat
   4. From a command prompt in the \Winnt\System32 folder, expand the Perfh009.da_ file by running the following:
      Expand.exe Perfh009.da_ Perfh009.dat
   5. Stop SQL Server.
   6. Using SQL Setup, turn performance monitor integration off by clearing the box beside SQL Perfmon Integration. (Please see step 1 in the Problems with Performance Monitor Setup section). Exit setup after the option changes.
   7. Using SQL Setup, turn performance monitor integration on by selecting the box beside SQL Perfmon Integration. (Please see step 1 in the Problems with Performance Monitor Setup section). Exit setup after the option changes.
   8. Start SQL Server.

SQL Server Performance Counters on Clustered SQL Servers

SQL Server Performance Monitor counters for the Virtual Server are not present when MicrosoftSQL Server 7.0 is set up with a virtual SQL Server configuration and the passive node has control of the resources. The counters are not available to the primary node until you shutdown and restart the whole cluster. Even then, availability is sporadic.

The SQL Server extension counters must be found when the system initially starts. Because the counters DLL file is located in the \Mssql\Binn folder for SQL Server 6.5 by default, the counters are not found when the initial system startup takes place. This occurs because the cluster drive in which SQL Server is installed is not accessible until the MSCS resources are all online. SQL Server 7.0 already places these counters in the proper folder, %Systemroot%\System32\ so that they are available. To make the Sqlctr65.dll file available, place a copy of the Sqlctr65.dll file in the %Systemroot%\System32 folder. The Sqlctr70.dll file is placed in this folder by default.

For additional information about SQL Server Performance Counters, click the article numbers below to view the articles in the Microsoft Knowledge Base: WARNING: If you use the following article, read it and compare it to other sections in the following Microsoft Knowledge Base article, which discusses rebuilding the SQL Server registry entries. Performance counters on clustered SQL Servers are not always available. When performance counters are available, they are usually only on the primary node if no failover has occurred.

SQL 6.5 Performance Counters with a Named SQL 2000 Instance

If SQL Server 2000 is installed as a named instance on the same computer as SQL Server 6.5, and the SQL Server 6.5 computer is not configured to listen on the TCP/IP Sockets network library, performance monitor counters for SQL 6.5 will not be available. By default, SQL Server 2000 will first attempt to make a connection to a server using sockets. The following two workarounds may be used:

• Configure SQL Server 6.5 to listen on the TCP/IP Sockets network library using port 1433. This can be done by using the SQL Setup program or SQL Enterprise Manager. SQL Server must be restarted after making this change before the counters will be available.
• Using the SQL 2000 Client Network Utility, change Named Pipes to be the first entry in the Enabled Protocols by Order list box.

REFERENCES

For more information on the RegistryRebuild option, please see the following article in the Microsoft Knowledge Base: For further information about performance counters, please see the following article in the Microsoft Knowledge Base: