SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability56
I/O is very slow
For nearly half of all SAP customers, performance escalations are related to I/O back-end problems. The most common observation is that SAP systems record extremely slow response times from the database. However, the CPU is not being utilized for the database.
Having enough bandwidth for reading and writing, combined with a fast response time, is vital, especially for high-end database servers. However, as it concerns the storage layout and I/O throughput capacity planning, bear in mind that a conventional SQL Server online backup creates an additional I/O workload by reading the data from the SQL Server data files. Not having any I/O bandwidth left for SQL Server online backup, can result in a severe performance effect to response times when running an online backup. For more information, see earlier in this paper.
The most common errors observed include:
Too few disks are utilized.
An incorrect partition offset was selected for Storage Area Network partitions.
Note that each SQL Server 2005 I/O results in multiple I/Os at the back end. Unless this factor is understood, the partitions for Storage Area Network storage might not be set up correctly. Consult with the Storage Area Network hardware vendor to determine the correct setup of storage for SAP with SQL Server 2005.
There are too few Host Based Adapters (HBA). The queue depth of the HBA is left as the default of 32, instead of setting it to 128 or even 255. Check with the storage hardware vendor for the proper settings and maximum values.
SecurePath or PowerPath software was incorrectly configured in failover mode, instead of workload balancing mode.
I/O Usage Patterns
Concerning disk usage patterns, SQL Server 2005 can read four different formats:
8-KB read. The most common format is a single page 8-KB read that is used with most SAP products having an OLTP characteristic (greater than 90 percent) workload.
64-KB read. The 64-KB format is typically used for tape media backup when a block format of 64 KB is recommended to resolve performance issues. This format reads a whole extent and can be observed frequently in SAP BW. In this case, SQL Server 2005 reads ahead during index scans.
256-KB read. SQL Server 2005 reads the allocation order within different data files, such as for index creation, using an allocation-oriented scan such as a table scan. 256-KB reads might be used in SAP BW. These reads are rarely seen in SAP products having an OLTP workload.
1-MB read. During the disk backup, SQL Server 2005 reads 1-MB blocks of data over the network to a central tape library to speed up the backup. 1-MB reads are SAP-independent.
Microsoft Corporation © 2005