SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability31
Number and size of SQL Server data files
The number of data files is chosen during SAP product installation. Three data files (the default that SAP typically uses) are appropriate for the majority of smaller SAP installations. In SQL Server 2005, data access performance within a data file is not related to the size of the data file, but to its concurrency on internal administration pages within the data file.
For the best performance, the ideal number of data files for the SAP installation is typically equal to the number of CPU or processor cores dedicated to SQL Server 2005 at a 1:1 ratio. For example, a dual-core processor such as an Advanced Micro Devices® (AMD) Opteron™ or dual-core Intel® Itanium® processor Montecito, counts as two CPUs. A four-processor dual-core HP ProLiant DL585 server counts as eight cores; therefore, eight data files are recommended.
Additional data files are sometimes required when there is a handling issue, such as when data files become too large to handle in copying or other activities.
For the best distribution, create the data files of the same size and distribute the files evenly. Each data file can be assigned to the same number of spindles, making the disk I/O system easy to maintain. When the data file size needs to be increased, increase each of the data files by the same amount. This allows you to maintain an approximately equal amount of free space in each file.
In addition, if data files are added to an established database, make the size of the new files equal to the free space of the existing data files. This allows SQL Server 2005 to distribute new data evenly between the old and new data files.
Considerations for defining the number and size of data files include:
Establishing a Unicode system. Use the 1:1 ratio between CPU cores and data files when installing a new system or migrating a system from non-Unicode to Unicode characters. Assume that the implementation of a particular SAP product typically increases in stages, indicating that a much larger database server might be required in the future.
Evaluating disk configurations. Evaluate the available storage hardware, including the number of disk arrays and partitions, and the amount of available storage space on disk arrays.
Estimating database growth. Consider the estimated database growth during production. To stay flexible in disk configurations, avoid using very large data files. Very large data files can create problems in handling, for example, setting up sandbox systems, copying, and so on.
Evaluating nonproduction environments. Keep in mind that each SAP production system is supported by test and development systems. This means that the test system must be periodically synchronized with the production database. A sandbox system that is based on the SAP production system might also be required.
Increasing the number of data files. In some cases, the infrastructure might require more than three data files with more CPU cores available. However, too many data files often increase monitoring requirements. The use of a large number of data files should be avoided. Most customers use from 4 to 24 data files.
Setting autogrowth. Using autogrowth is highly recommended for all data files, even if the autogrowth of a data file limits the proportional fill functionality of SQL
Microsoft Corporation © 2005