SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability58
SQL Server 2005 I/O performance
SQL Server 2005 measures I/O performance as a built-in function. For example, notice the SQL command:
select * from ::fn_virtualfilestats(<db_id>,-1)
This gives the number of read I/Os and write I/Os and the bytes read and written to each file of the specified database. The stall time of read or write activity for each file is calculated as the number of bytes read divided by the sum of the “read stall time” or the number of bytes written divided by the sum of the “write stall time.”
The result of the SELECT statement can indicate if the proportional fill is working properly, as described in earlier in this paper.
The output also indicates whether all files show similar performance. The numbers get accumulated over the entire run time of the SQL Server instance from startup. For this reason, these numbers can be much lower than those indicated by current Windows System Monitor measurements. The accumulated output data might not reflect timeframes with poor I/O time accurately and the data cannot be reset.
Slow SAP transactions hamper the user experience
When only a few transactions are slow, restrict the problem to specific SAP transactions or jobs. The SAP NetWeaver Application Server design assigns the user request to a SAP process at run time. If the problem can be reproduced, the executions can be traced by using the SAP ST05 SQL Trace transaction to filter conditions such as the user name, a specific SAP process within an instance, or a specific SAP report. The ST05 transaction runs locally on the application server. ST05 can trace activity on the specific application server on which it runs.
ST05 SQL Trace transaction
Figure 20 shows the ST05 transaction with the SQL statements that were executed against the database. The duration is shown in microseconds.
For many statements, two or three lines are shown to characterize the different phases of statement execution. The SAP Open phase is typically used to determine the performance of the execution.
Microsoft Corporation © 2005