Version:15
Validity: valid since 20.12.2005
Symptom
The initial screen of the SQL Server Database Monitor (transaction ST04) shows incorrect values, for example:
1. Values higher than 100 % or negative values for the 'Data cache hit ratio' or 'Procedure cache hit ratio'.
2. 0 for several counters such as 'Memory Usage', 'Current memory', 'Procedure cache' and 'hit ratio'.
3. Your system runs Release 6.20 with Basis Support Package 54-57. The 'Procedure cache hit ratio' on the initial screen of transaction ST04 displays incorrect values.
4. You are using SQL Server 2000 Service Pack 3 (Build 8.00.760). The following message is written at the start in the SQL Server Errorlog
Performance monitor shared memory setup failed: -1
And the SQL server performance counters are missing, as described above.
Performance monitor shared memory setup failed: -1
And the SQL server performance counters are missing, as described above.
5. You are using SQL Server 2000 Service Pack 2 (Build 8.00.534) or a higher build. The 'Data Cache Hit Ratio' frequently displays nonsensical values (as described in point 1. above) or sometimes changes very rapidly.
6. Another value, for example, 'Request buffer pages' is displayed with a negative value.
7. The system displays nonsensical values for 'CPU idle' or 'CPU busy' (for example, a negative value for 'CPU idle' or the value for 'CPU busy' is higher than the value for 'CPU idle' even though the server was only operating at medium capacity).
At the same time, transaction RZ20 displays high values for 'CPU Used by SQL' (sometimes more than 100 %).
At the same time, transaction RZ20 displays high values for 'CPU Used by SQL' (sometimes more than 100 %).
8. Your system uses Release 6.20. The first line of the initial screen displays an incorrect product version of SQL Server (for example, 'Enterprise Edition' instead of 'Datacenter'). The windows boot parameters 'PAE' and '3GB' may also be displayed incorrectly. An example:
Microsoft SQL Server 2000 - 8.00.929 (Intel IA-64) Enterprise Edition 3 GB PAE
Microsoft SQL Server 2000 - 8.00.929 (Intel IA-64) Enterprise Edition 3 GB PAE
9. Your system runs Release 6.40 or 7.00. The 'Windows Memory Option' field displays 'AWE' or 'PAE' multiple times.
Other terms
Data Cache Hit ratio, Procedure Cache Hit ratio, cache hit ratio, > 100 %, negative values, performance counter, ST04, SQL Server, database monitor, SP2, 8.00.534, request buffer pages, @@idle, CPU idle, Enterprise Edition, PAE
Reason and Prerequisites
Your database version is SQL Server 2000.
1. The Data cache hit ratio and Procedure cache hit ratio are calculated as a quotient from two entries in the system table master..sysperfinfo. The figures are saved here as 4-byte integers. These values can overrun. Normally, this only happens after SQL Server has been running for several weeks, but it can occur within a few days of starting the system if there is high database activity or you use SQL Server 2002 SP2. The database monitor may then display negative values for a short time, and then values greater than 100% for the cache hit ratio. The system returns incorrect values greater than 100% for the procedure cache hit ratio total. This error has not been observed for the cache hit ratio for individual cache objects (such as 'execution context').
2. Many values in the database monitor are based on performance counters for SQL servers. The SQL server performance counters may not be correctly installed. You can check this by displaying the sysperfinfo table (ST04 -> Detail Analysis -> System tables -> sysperfinfo). The problem occurs if the table is empty. Alternatively, you can call Windows System Monitor (formerly NT performance monitor) and check whether performance counters for SQL Servers are available. There is a problem if no counters are present.
a) This error can occur, for example, after an SQL Server upgrade or installation of an SQL Server or Windows Service Pack.
b) The problem also occurs in a Microsoft Cluster Installation with SQL Server 7.0 on Windows NT 4.0: The performance counters are only available on one of the two cluster servers (see Note 112266 and Microsoft Knowledge Base Article Q254321).
3. This program error was imported as part of Basis Support Package 54, and is solved by Basis Support Package 58 (SAPKB62058) in SAP Note 902892.
4. A conflict exists with SAPOSCOL with SQL Server 2000 SP3: If you start the SQL Server while the SAPOSCOL is running, the described problem occurs. (Microsoft describes a related problem in the Knowledge Base Article Q812915)
5. With SQL Server 2000 Service Pack 2, changes were made to the numbering of the performance counter for the Data Cache Hit Ratio. As a result, the system no longer counts cache accesses since the SQL Server startup and frequently bisects the number of accesses and hits. This means that the hit ratio often relates to an unknown (and usually very short) period and cannot be interpreted in a useful way. Consequently, the database monitor (ST04) frequently displays nonsensical values.
6. Also 'request buffer pages ' and other counters are saved by the SQL Server as 4 byte integers. After a certain period, the value overflows (to higher than 2147483648) and is displayed as a negative value.
7. The 'CPU idle' and 'CPU busy' values are based on the '@@idle' SQL Server system function. This variable overflows if SQL Server runs for 49 days consecutively. (On servers with 16 or more CPUs, '@@idle' may overflow sooner than this).
8. A program error occurs when you call System Stored Procedures. This is corrected in Basis Support Package 54 (SAPKB62054).
9. When you choose 'Refresh' the option is reattached to the field.
Solution
1. Cache hit ratios
- Data cache hit ratio: As of SQL Server 2000 Service Pack 4, there is no longer a solution. You can use the following command:
DBCC sqlperf(lrustats)
This command uses internally a larger datatype as a 4-byte integer, and generally displays the correct data cache hit ratio since SQL Server has started. Using
DBCC SQLPERF(LRUSTATS,CLEAR)
you can reset the performance counter, on which the cache hit ratio depends, to zero. After that, the database monitor (transaction ST04) displays useful values again - specifically, the cache hit ratio since the reset.
- Procedure Cache Hit ratio: Use the command
DBCC CACHESTATS
The line 'Summary' contains the procedure cache hit ratio since SQL Server has started.
2. Missing SQL Server performance counter
a) You can often restore the SQL Server performance counters with the following procedure. To uninstall and then reinstall the SQL Server performance counters, use the programs unlodctr and lodctr that are located under %SystemRoot%\System32\ (usually C:\WINNT\System32). You also require the sqlctr.ini file located in the bin directory of the SQL Server.
For SQL Server 7, Microsoft Knowledge Base Article Q196657 describes the following procedure:
For SQL Server 7, Microsoft Knowledge Base Article Q196657 describes the following procedure:
- Open a command line.
- Execute the following command to uninstall the SQL Server performance counters:
UNLODCTR MSSQLSERVER
(For an SQL Server 2000 Named Instance, this command needs to be adjusted.)
- Execute the following command to install the SQL Server performance counter (adjust the path for the SQL Server BINN directory or go to the BINN directory to execute the LODCTR command):
LODCTR C:\MSSQL7\BINN\SQLCTR.INI
b) As a workaround, you can run the SQL Server on the server in the cluster containing the performance counters.
3. If you have not yet implemented the correction instructions from Note 902892, you can still read the correct value in Transactions RZ20 (SQL Server -> Performance -> Cache -> Procedure Hit Ratio), or ST04 ( -> Detail Analysis Menu -> Server Detail -> Cache Details). In the first line of this display, the system shows 'Misc Normalized Trees'), which is displayed incorrectly in the initial screen. The 'Total' or 'SUM w/o TOTAL' rows contain the cache hit ratio for the entire procedure cache. 'Total' is calculated from the two corresponding values in master..sysperfinfo, and may be incorrect (for example, it may be greater than 100%). 'SUM w/o TOTAL' is calculated by the SAP Database Monitor from the values for all other cache objects in master..sysperfinfo, and we have found that it shows useful values.
4. Install SQL Server Hotfix build 8.00.780 in accordance with note 608636.
As a workaround, you can proceed as follows to start SQL Server 2000 Service Pack 3 with performance counters:
As a workaround, you can proceed as follows to start SQL Server 2000 Service Pack 3 with performance counters:
a) Stop the SQL Server.
b) Stop SAPOSCOL.
c) Start the SQL Server.
d) Start SAPOSCOL.
In order to retain the required sequence automatically when booting, you can define the SAPOSCOL service as depending on the SQL Server Service.
In order to retain the required sequence automatically when booting, you can define the SAPOSCOL service as depending on the SQL Server Service.
5. As of SQL Server 2000 Service Pack 4, there is no longer a solution. Alternatively, ignore the data cache hit ratio in transaction ST04 and use the workaround described under point 1, above. You can also ignore the data cache hit ratio in transaction ST04 and use the work around mentioned in section 1.
6. As of Release 6.10, the overflow is registered by the SAP System and the correct value of 'Request buffer pages' is displayed. As of Release 6.30, this applies to all values in the ST04 initial screen.
Up to and including Release 4.6D, there is no workaround to determine the correct value.
Up to and including Release 4.6D, there is no workaround to determine the correct value.
7. A solution does not exist for the '@@idle' function. As a workaround, you can restart the SQL Server.
8. Import Basis Support Package 54.
9. Program correction as described in Note 896896.
Affected Releases
|
Download original format @ https://service.sap.com/sap/support/notes/515376
No comments:
Post a Comment