Compteurs performance SQL

Bonjour,

Ne voyant pas l'article utilie en externe je le colle ici,
le formattage va sans doute être perdu :
How to troubleshoot missing SQL Server Performance Monitor
counters for WGID:130
ID: 824616.KB.EN-US CREATED: 2003-07-16 MODIFIED:
2004-03-24


Public | kbAudDeveloper


========================= ========================= =========
====================
-----------------------------------------------------------
--------------------
The information in this article applies to:

- Microsoft SQL Server 2000 (all editions) SP3a (Version:
SP3a)
- Microsoft SQL Server 2000 (all editions)

-----------------------------------------------------------
--------------------




Important This article contains information about
modifying the registry.
Before you modify the registry,
make sure to back it up and make sure that you
understand how to restore the
registry if a problem occurs. For information
about how to back up, restore,
and edit the registry, click the following
article number to view the
article in the Microsoft Knowledge Base:

KBLink:256986.KB.[LN]: Description of the
Microsoft Windows Registry


IN THIS TASK
------------
- #2: SUMMARY
- #3: Troubleshoot missing performance counters
- #4: Troubleshoot missing or incorrect

registry values
- #5: Troubleshoot missing, corrupted, or
incorrect versions of the Performance Monitor files
- #6: Restore missing

performance counters
- #7: Understand SQL Server performance counters in SQL
Server clusters
- #8: REFERENCES




SUMMARY
=======

When you use performance counters for an instance of
Microsoft SQL Server 2000 in SQL Server
Performance Monitor, one or
more of the SQL Server-related
performance counters are missing. This
step-by-step article describes how to
troubleshoot the missing performance
counters.

#1: back to the
top

Troubleshoot missing performance counters
-----------------------------------------

Several possible causes exist for the missing
performance counters in SQL Server
Performance Monitor. This article includes the most common
causes for missing performance counters and provides the
steps to resolve each cause.




Troubleshoot missing or incorrect
registry values

The registry values that are related to the performance
counters may be missing or may have
values that are not correct. To troubleshoot the
problems in the registry, follow these
steps.

Warning If you use Registry Editor incorrectly, you may
cause serious
problems that may require you to
reinstall your operating system. Microsoft
cannot guarantee that you can
solve problems that result from using Registry
Editor incorrectly. Use Registry
Editor at your own risk.
If the Performance Library registry entry is missing or
is not correct, follow these steps:


1. Start Registry Editor.

2. For a default instance of SQL Server, locate the
following registry value in Registry Editor:

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetService
sMSSQLServerPerform
anceLibrary For
a named instance
of SQL Server, locate the following registry value in
Registry Editor:

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetService
sMSSQL$<Instance
Name>PerformanceLibrary If the registry key
does not exist in
Registry Editor, you must rebuild the registry.
For more
information about how to rebuild the registry to fix
these registry
errors, see the "How to rebuild the registry (Setup)"
topic in SQL Server Books
Online.

3. Make sure that the data in the Library registry value
is the
absolute path of the Sqlctr80.dll file. By default, the
Sqlctr80.dll
file exists in the C:Program FilesMicrosoft SQL
ServerMSSQLBinn
folder.

4. Make sure that the
Performance
registry key
has the correct
security permissions:
- Grant Full Control permissions to the
Administrators account.
- Grant Special Access permissions to the
Everyone account.
- Grant Full Control permissions to the
System account. Note To modify the security permissions of
a registry key in Registry
Editor, locate
and then click the registry key, and then
click "Permissions" on the
"Security" menu.


If the Performance Library Counter registry entry is
missing or is not correct, follow these steps:


1. Start Registry Editor.

2. In Registry Editor, locate the following registry
key:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindows
NTCurrentVersionPerflib09Counter If the
registry
key does not
exist in Registry Editor, you must rebuild the
registry. For
more information about how to rebuild the registry
to fix these
registry errors, see the "How to rebuild the registry
(Setup)" topic in SQL Server Books
Online.

3. Check to see if the Counter registry value contains all
the SQL
Server performance counters.

4. Make sure that the correct security permissions have
been granted to the

HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindows

NTCurrentVersionPerflib09 registry key:
- Grant Full Control permissions to the
Creater Owner account.
- Grant Full Control permissions to the
Administrators account.
- Grant Read permissions to the Everyone
account.
- Grant Full Control permissions to the
System account.

5. Make sure that the
HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindows
NTCurrentVersionPerflib09 registry key contains
values for every
instance that are appended together.


If the Performance registry key is missing or is disabled,
follow these steps:


1. Start Registry Editor.

2. For a default instance of SQL Server, locate the
following registry
key in Registry
Editor:

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesMSSQLS
erverPerformance For
a named instance
of SQL Server, locate the following registry key in
Registry Editor:

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetService
sMSSQL$<Instance
Name>Performance

3. Make sure that the Performance registry key contains at
least 9 registry
values, including the following:

- Open
- Library
- Collect
- First Counter
- First Help
- Last Counter
- Last Help
- Close

4. If the Disable Performance Counters registry value
exists under the
Performance registry key, make sure that the data in
the Disable
Performance Counters registry key is not set to 1.


#1: back to the
top


Troubleshoot missing, corrupted, or incorrect versions of
the Performance Monitor files

Missing, corrupted, or incorrect versions of SQL Server
Performance
Monitor files may cause the loss of the
SQL Server performance counters. To
troubleshoot problems that are related
to SQL Server Performance Monitor files, use the
following list:


- The Sqlctr80.dll file may be corrupted or you may have
an incorrect
version of the file. To resolve this problem, copy the
Sqlctr80.dll
file from the x86System folder of the SQL Server Setup
folder.

- Make sure that the absolute path of the Sqlctr80.dll
file is included
in the Windows Path environment variable.

- The Perfc009.dat and the Perfh009.dat SQL Server
Performance Monitor data files may be corrupted.

Note All files that contain "009" are used when the
default language
is English. These .dat
files refer to the English language files. Other
languages may have
different numbers.

To resolve this problem, follow
these steps:


1. Locate the Perfc009.dat file and the
Perfh009.dat file on your

computer, and then rename the files.

2. Locate the following files in the

Microsoft Windows 2000 Server Setup folder, and then copy
the files to the <Installation Drive>:WINNTSystem32
folder on your computer:

- Expand.exe
- Perfc009.da_
- Perfh009.da_

3. At a command prompt, locate the
<Installation Drive>:WINNTSystem32 folder on your
computer.

4. Run the following commands at the command
prompt to

extract the Perfc009.dat file and the Perfh009.dat file:

expand.exe Perfc009.da_ Perfc009.dat

expand.exe Perfh009.da_ Perfh009.dat

5. Restart the SQL Server service.


#1: back to the
top


Restore missing
performance
counters

If you cannot identify the problem that causes the loss of
the Performance Monitor counters in your
instance of SQL Server,
follow these steps to restore the SQL
Server-related performance counters on
your computer:

1. If SQL Server Performance Monitor is running on your
computer, close
it.

2. Stop the services that may be affected when you load
the SQL Server
performance counters, including the Simple Network
Management Protocol
(SNMP) service and the antivirus software service that
is running on
the computer.

3. Locate the following files on the Microsoft SQL Server
2000
Setup CD:

- Sqlctr.h
- Sqlctr.ini
- Sqlctr80.dll
Note You can find the Sqlctr.h file and the Sqlctr.ini
file in the x86Binn folder in the SQL Server Setup
folder. You can find the Sqlctr80.dll file in the x86
System folder in the SQL Server Setup folder.

4. For a default instance of SQL Server, copy these files
from the SQL
Server 2000 Setup CD to the <Installation
Drive>:Program
FilesMicrosoft SQL ServerMSSQLBinn folder on your
computer. For a
named instance of SQL Server, copy these files from the
SQL Server 2000
Setup CD to the <Installation Drive>:Program
FilesMicrosoft SQL
ServerMSSQL$<Instance Name>Binn folder on your
computer.

5. If you are restoring the performance counters for a
named
instance of SQL Server,
edit the Sqlctr.ini file so that the
entries in the Sqlctr.ini
file reference the named instance of SQL Server.
For example, the entries in
the Sqlctr.ini must be
similar to the following for a named instance of SQL
Server:

[info]
drivername=MSSQL$<InstanceName>
symbolfile=sqlctr.h

[languages]
009=English

[text]

BUFMGR_OBJECT_009_NAME=MSSQL$<InstanceName>:Buffer
Manager
BUFMGR_OBJECT_009_HELP=Statistics related
to SQL Servers buffer manager
Note By default, the entries in the
Sqlctr.ini file refer to the
default instance of SQL
Server.

6. At a command prompt, locate the Binn folder for your
instance of SQL
Server. For a default instance of SQL Server, this
folder is located in
the <Installation Drive>:Program FilesMicrosoft SQL
ServerMSSQLBinn
folder. For a named instance of SQL Server, this file
is located in the
<Installation Drive>:Program FilesMicrosoft SQL
ServerMSSQL$<Instance Name>Binn folder.

7. For a default instance of SQL Server, run the following
command at the command prompt to unload
the SQL Server performance
counters:

unlodctr MSSQLServerFor a named instance
of SQL Server, run the following command at the command
prompt to unload
the SQL Server performance
counters:

unlodctr MSSQL$<InstanceName>Note The
Unlodctr.exe file and the Lodctr.exe file can be found in
the <Installation Drive>:WINNTsystem32 folder on the
computer that is running SQL Server.

8. For a stand-alone server, run the following command at
the command prompt to load the
SQL Server performance
counters:

lodctr sqlctr.iniFor a clustered server,
run the following command at the command prompt to load
the SQL Server performance counters (where
\<VirtualServerName> is the unique virtual server name
of the SQL Server cluster):

lodctr \<VirtualServerName>
sqlctr.iniNote On a clustered server, Lodctr.exe takes the
virtual server name instead of the instance name.

Note You can also unload and reload the SQL Server-related
performance
counter for a specific
instance of SQL Server by using a batch file.
If you use a batch file,
you can also use error handling to trap errors
to troubleshoot problems.

For example, the following batch file
can be used for a named
instance of a stand-alone server:

unlodctr MSSQL$<MyInstanceName>
net helpmsg %errorlevel%
lodctr sqlctr.ini
net helpmsg %errorlevel%


9. Restart the SQL Server service for the instance of SQL
Server.

10. Make sure that the SQL Server performance counters are
successfully loaded. To do
so, use the Exctrlst.exe program that is included in the
Windows 2000 Resource
Kit.

Note For download information and additional information
about the Exctrlst.exe program, visit the following
Microsoft Web
site:

http://www.microsoft.com/windows2000/techinfo/reski
t/tools/existing/exct
rlst-o.asp


#1: back to the
top

Understand SQL Server performance counters in SQL Server
clusters
-----------------------------------------------------------
------

SQL Server Performance Monitor counters for the virtual
server are not present when SQL Server
is set up with a virtual
SQL Server configuration and when the
node does not currently own the specified SQL
Server group. The instance-specific
counters are not available to
the primary node until you shut down and
then restart the whole cluster. Even after you do so,
the availability of performance counters
is sporadic. Performance counters are
generally available on primary node if
no failover has occurred.

For example, if SQL Server is running on a cluster with
nodes Node1 and
Node2, and you are running on Node1, you
must load the performance counters
by using the LODCTR command on Node1.
You must failover the cluster to Node2 and
make Node2 the primary node of the
cluster. After you do so, run the same LODCTR
command on Node2, and then make Node1
the primary node of the
cluster. The Performance Monitor
counters are now
available on Node1. In a production
environment, this method may create problems. However,
there is
no way to work around the failover.

#1: back to the top

REFERENCES
==========



For additional information, click the following article
numbers to view the articles in the Microsoft Knowledge
Base:



KBLink:127207.KB.[LN]: Missing objects and
counters in Performance
Monitor


KBLink:227662.KB.[LN]: PRB: SQL Performance
Monitor counters missing


KBLink:157805.KB.[LN]: BUG: RegistryRebuild option
of Setup is not
documented


KBLink:170394.KB.[LN]: BUG: SQL Server perf.
counters don't work on
WinNT Server 4.0


KBLink:812915.KB.[LN]: FIX: "Performance monitor
shared memory setup
failed: -1" error message when you start SQL Server
#1: back to the
top

QUERY WORDS
===========

perfmon sql performance counter



========================= ========================= =========
====================


Publishing Keywords : kbAudDeveloper kbSQLServ2000
kbSQLServ2000Search kbSQLServ2000SP3a kbSQLServSearch
Keywords : kbDSXGlobal2003Swept
kbDSWNET2003Swept kbHOWTOmaster kbDatabase
kbPerformanceTool kbPerfMon kbPerformance kbRepair
kbRegistry kbWiproTR kbWiproAuthor kbServer kbSysAdmin
Revision Type : Major
Workgroup : DS D- SQL Server (Data Services)
[130]
Billing Product : SQL Svr Standard Edtn 2000 EN
[2654]
Original Language : EN-US
Source Language : EN-US
========================= ========================= =========
====================

-----Message d'origine-----
Je dispose d'un server Windows 2003 standard, SQL 2000
Enterprise SP3a, MDAC 2.8. Le serveur n'est pas en en
cluster.
Les compteurs de performances SQL ont disparu.
J'ai trouvé une procédure afin de restaurer ces compteurs
sur le site de microsoft mais concerne SQL 6.5.
Est-ce que quelqu'un à une idée ?

Merci à tous.
.