PRB: SQL Performance Monitor Counters Missing

ID: Q227662


The information in this article applies to:

IMPORTANT: This article contains information about editing the registry. Before you edit the registry, make sure you understand how to restore it if a problem occurs. For information about how to do this, view the "Restoring the Registry" Help topic in Regedit.exe or the "Restoring a Registry Key" Help topic in Regedt32.exe.

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:


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


  4. 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

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 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

    WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

    For information about how to edit the registry, view the "Changing Keys and Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT, you should also update your Emergency Repair Disk (ERD).
    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

    For more information on the RegistryRebuild option, please see the following article in the Microsoft Knowledge Base:
    Q157805 BUG: RegistryRebuild Option of Setup Is Not Documented
    For further information about performance counters, please see the following article in the Microsoft Knowledge Base:
    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 

    Last Reviewed: May 26, 1999