SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability32
Server over the data files. It is better to have some unevenly distributed data than to have SAP components such as mySAP stop running. When automatic growth is used, set each data file to a 10 percent growth allowance.
Figure 12 shows the proportional fill feature, which is used to spread the data between the data files according to each file’s available free space.
The considerations for using the proportional fill include:
Extending the data file size. As volume increases, manually extend the size of the data files by the same amount. Assume that each file was created to the same size.
Proportionally fill the data files. When data is inserted into the database, the data files are filled proportionally using the free space in each data file.
If the data files were created to the same size, SQL Server 2005 distributes the data of each table evenly over the existing files. This makes the read/write workload even, simplifies placing the data files over a storage backend, and avoids hot spots on specific disks.
Recalculating the proportional fill factor. Periodically, SQL Server recalculates the proportional fill factor for each file based on the available free space. When one of the data files becomes full, the proportional fill goes out of balance. In this case, it fills the other files, grows only one of the files, and fills this file. Then it grows the next file, and so on.
Microsoft Corporation © 2005