PRB: SQL Performance Monitor Counters Missing

• Microsoft SQL Server version 6.5

SYMPTOMS

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

Source: SQLCTR60
Event Id: 7
Description: DB-LIBRARY - SQL Server message: Login failed

Source: SQLCTR60
Event Id: 8
Description: DB-LIBRARY error - Login incorrect

Source: SQLCTR60
Event Id: 2
Description: OpenSQLPerformanceData: Cannot Connect to SQL Server - [ServerName]

Source: SQLCTR60
Event Id: 9
Description: CollectSQLPerformanceData: NULL dbProc Encountered

Source: SQLCTR60
Event Id: 10
Description: CollectSQLPerformanceData: Connection lost, attempting to reconnect to SQL Server [ServerName]

Source: Perflib
Event Id: 1011
Description: The library file "" specified for the "MSSQLServer" service could not be opened. Performance data for this service will not be available. Status code is data DWORD 0.

CAUSE

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

• Problems with the initial setup of SQL Performance Monitor.



• Problems with the login that SQL Performance Monitor uses.



• Problems with the registry.



• Problems with the files SQL Performance Monitor uses.

MORE INFORMATION

Problems with Performance Monitor Setup

1. Make sure SQL Performance Monitor integration is on.
   
   
   1. Run SQL Setup on the SQL Server.
   
   
   
   2. Click Continue.
   
   
   
   3. Click Continue.
   
   
   
   4. Choose Set Server Options.
   
   
   
   5. Click Continue.
   
   
   
   6. If an X does not exist in the box beside SQL Perfmon Integration, select the box.
   
   
   
   7. Click Change Options.
   
   
   
   8. Click the button to Exit to Windows NT after the options have been changed.
   
   
   
   9. Stop and restart SQL Server.
   
   
   
   NOTE: If SQL Perfmon Integration is already selected, try clearing it, changing the options, exiting setup, and then performing 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 directory 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\PerfMonSecure


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 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 the button to Exit to Windows NT after the options have been changed.



10. Stop and restart SQL Server.

Problems with the Performance Monitor Login

The server is using integrated security

-or-

The registry has been modified such 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 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 script Procsyst.sql located in the \MSSQL\Install directory to create the login with the correct permissions.



2. Make sure 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 the default database for probe is the master database by issuing the following query from ISQL/w:

   
   sp_defaultdb probe, master 




4. If default permissions for the public group or for the probe login have changed, use ISQL/w to run the script Procsyst.sql located in the \MSSQL\Install directory to give correct permissions to the probe login.



5. Make sure the 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 script Procsyst.sql located in the \MSSQL\Install directory to create the probe login and user with the correct permissions.

Problems with the Registry

1. Use Regedt32.exe to check the following registry key:
   
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\Performance\Library
   
   
   1. Make sure this key exists. If it does not, rebuild the registry by running the following in the MSSQL\BINN directory from a command prompt:

      setup /t RegistryRebuild = On

      NOTE: It is recommended that you make a complete backup of your system before performing the registry rebuild.
      
      
   
   
   
   2. Make sure 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 permissions are correct on the key. In the registry editor, highlight the performance folder, then from the top menu, click security and select permissions. Make sure the following users have the correct 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\Counter
   
   
   1. Make sure this key contains all SQL Performance Monitor counters. If not, rebuild the registry 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\MSSQLServer
   
   
   HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SecurePipeServers\winreg\AllowedPaths
   
   Make sure 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 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 running Windows NT 4.0, 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 using the following steps:
   
   
   1. In the \Winnt\system32 directory, rename the following files:
      
      

      
      Perfc009.dat
      Perfh009.dat 

      For more information, please see the following article in the Microsoft Knowledge Base:

      Q127207 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 directory on the local computer.
   
   
   
   3. From a command prompt in the \Winnt\system32 directory, expand the Perfc009.da_ file by running the following:

      
      Expand.exe Perfc009.da_ Perfc009.dat 

   
   
   
   4. From a command prompt in the \Winnt\system32 directory, 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 has been changed.
   
   
   
   7. Using SQL Setup, turn performance monitor integration on by selecting the box beside SQL Perfmon Integration. (Please see step 1 under Problems with Performance Monitor Setup). Exit setup after the option has been changed.
   
   
   
   8. Start SQL Server.
   
   
   

REFERENCES

Q157805 BUG: RegistryRebuild Option of Setup Is Not Documented

Q170394 BUG: SQL Server Perf. Counters Don't Work on WinNT Server 4.0

Additional query words: perfmon

Keywords :
Version : winnt:6.5
Platform : winnt
Issue type : kbprb