PRB: Performance Monitor for SQL Performance Counters Missing

  - Microsoft SQL Server version 4.2

SYMPTOMS

The SQL Server for Windows NT performance counters may, under some conditions, not be visible when PERFMON.EXE (Performance Monitor) is run from a Windows NT network client. This is most frequently seen following a restart of the server machine.

CAUSE

SQL NT monitors a wide variety of SQL-specific performance counters. These counters are visible as objects in the Performance Monitor (perfmon) program. If a Windows NT network client is monitoring either SQL or non-SQL performance counters, and if the server machine is restarted, thereafter, the SQL performance counters may not be visible. They will be visible if perfmon is run locally on the server machine.

The extensible SQLCTRS.DLL acts as the interface between SQL Server and perfmon and is what exports the counters. If perfmon is run locally, the ADVAPI32.DLL process is responsible for loading any perfmon extensible DLL. If perfmon is run from a network client, SCREG.EXE is responsible for loading any perfmon extensible DLL. ADVAPI32 and SCREG are not part of SQL Server, but are Windows NT components.

SCREG.EXE can become unable to load the SQLCTRS.DLL if perfmon sessions are running on network clients and if the server machine is restarted. You may see one or both of these errors in the Windows NT application event log:

   Event ID 4005:
   Load of a required DLL failed. Make sure the DLL file is in
   the PATH. DOS Error number is returned in the data.

   Event ID 2:
   OpenSQLPerformanceData: Cannot Connect to
   SQL Server - SERVERNAME

   where SERVERNAME is the name of your SQL Server.

WORKAROUND

Following a restart of the server machine, if any perfmon sessions are running against it from network clients, you may need to momentarily discontinue these sessions until SQL NT starts. If SQL NT has already started, you can simply momentarily shut down any perfmon sessions running on network clients. This will immediately restore the ability to monitor SQL NT performance counters, and further perfmon use can continue as before.

The perfmon sessions running on network clients can be shut down manually, or remotely via several ways.

1. From the server, run the NET FILE command. Current perfmon connections can be identified as having open the file \PIPE\winreg. Close each such session with the NET FILE /close command. See your Windows NT documentation for more information on this command.

2. From the server, run Control Panel server applet, and press the "In Use" button. Identify and close each perfmon connection.

3. From a Windows NT network client, run the Server Manager program, SRVMGR.EXE. Connect to the server machine and close any existing perfmon connection. Server Manager comes with Windows NT Advanced Server, and is also available in the Windows NT Resource Kit. See these sources for more information on Server Manager.