SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability57
SAP products having an OLTP type of workload require a balanced system layout with excellent I/O performance on the database side:
The read pattern primarily includes 8-KB pages and random reads from SQL Server 2005.
For writes, the transaction log performance must be excellent, with reads in 1 KB to 64-KB formats. On data files, the queuing tables require superior write performance while inserting data.
SAP BW and mySAP SCM are applications where read performance is very important and the most critical reads are performed in a 64-KB format. These particular SAP products typically perform critical queries such as joins over various tables. In addition, time critical processes, such as in SAP BW, require excellent write performance. In SAP BW, performance on tempdb is also critical.
Windows System Monitor can determine the average I/O performance values. In this case, measure I/O performance for at least 15 minutes, usually for one hour minimum, to ensure that the values are representative of actual performance.
In high-end systems, I/O SQL Server data files perform in the following range:
Up to 10 ms is good.
10 to 20 ms is acceptable. Less than 20 ms is acceptable for an I/O against data files.
20 to 30 ms is not acceptable. A time is greater than 30 ms causes alarm and triggers an investigation.
Above 30 ms shows an effect on the performance of the system and it is usually not acceptable, especially in high-end systems.
SQL Server transaction log file performance can be categorized in the following range:
Up to 10 ms is good.
10 to 20 ms is acceptable. A duration that is longer than 20 ms can result in problems using CPU resources.
Over 20 ms indicates that CPU resources of the database server are not fully utilized.
SQL Server 2005 has at least three 64-KB buffers to which the SQL threads write their transaction log entries. If one thread executes a commit, the transaction log buffer that is holding the commit record must be flushed. The thread writing the commit record must wait until the acknowledgement of a successful I/O write of the buffer is returned by the I/O subsystem.
If this takes a long time, the thread waits and it cannot be used. Typically, there are more than three buffers when more than four CPUs are on the server. When there is extremely low I/O performance against the log files, situations can occur where there is no free buffer and a large number of threads are waiting for I/O to be acknowledged. This indicates that available CPU resources are not being leveraged.
Microsoft Corporation © 2005