SAP Note 608636 - SQL Server does not maintain Performance Counter values

Version: 2 Validity: Valid Since 12.11.2010

Symptom

SQL Server can occasionally encounter problems in reading the Windows Performance Registry entries. When this happens SQL Server will typically record the following error in SQL Server errorlog:

'Performance Monitor Shared memory setup failed: -1'

Additionally this means that some significant performance data will not provided by SQL Server as well

Other terms

Performance Counters, SAPOSCOL, Performance Monitor, S2K, SP3, Shared Memory', errorlog, fn_virtualfilestats, sysperfinfo, lodctr

Reason and Prerequisites

Applications that usually attach to the Windows Performance Registry are:

1. SAPOSCOL running on the same server with SQL Server

2. Windows Performance Monitor running locally or remote collecting data on this specific server

If any of these products are already running during SQL Server startup, the error mentioned might be observed in the SQL Server errorlog. SQL Server not maintaining Performance values will also lead to:

- ST04: Values are missing. E.g Cache Hit Ratio is not maintained

- Running 'SELECT * FROM master..sysperfinfo' in SQL Server Query Analyzer will return with 0 rows

- Running 'SELECT * FROM ::fn_virtualfilestats(-1,-1)' in SQL Server Query Analyzer will return with 0 rows. Or for versions

after SQL Server 2000, the related meta data query 'SELECT * FROM sys.dm_io_virtual_file_stats(-1, -1)' will similarly return 0 rows.

It is very rarely encountered after SQL Server 2000 sp4. But in some situations it still can exist.

Solution

As one workaround, other applications or services that attach to the Windows Performance Registry should be stopped before starting SQL Server.

In a SAP environment we namely talk about SAPOSCOL and/or Windows Performance Monitor. After having SQL Server started, these application or services can be restarted without any problems and impact on SQL Server.

Though this workaround does not address typical SAP configurations that work on Microsoft Cluster Services (MSCS)

Would there still be some problems in displaying SQL Server Perfromance Counters in Windows Performance Monitor a reload of the SQL Server Perfromance Counters might be helpful. In order to perform this follow these steps:

o Open a command window and change to the ...\mssql\binn directory

o Check for the file sqlctr.ini.

o Execute the command 'lodctr sqlctr.ini'

o The command should return a message telling that SQL Server

Counters were loaded or that they have been loaded already.

This usually fixes the problem of not seeing SQL Server Performance Counters in Windows Performance Monitor

The Note is release-independent