File allocation extension in SQL Server 2000 (64-bit) and SQL Server 2005 (329526)

SUMMARY

The Microsoft SQL Server 2000 Enterprise Edition (64-bit) and Microsoft SQL Server 2005 Enterprise Edition releases support a new server startup option, -E. With the -E startup option, an administrator can increase the number of extents allocated per file in the filegroup during each iteration of the round robin cycle used to distribute extents across files. This extension may increase the performance for data warehouse (DW) applications by providing better data contiguity, and allowing the read-ahead mechanism to reduce the number of I/Os processed during sequential scans executed for DW queries.

Use of the -E option may be helpful for data warehouse applications that have a limited number of users running index or data scans. The option must not be used in other environments because there may not be a performance benefit, and it may affect performance in the other environments.

Although the -E option is functional in 32-bit releases of SQL Server, it is not supported.

MORE INFORMATION

The default allocation mechanism allocates one extent per file, using a round robin proportional fill factor algorithm, before it switches to the next file in the filegroup.

In the SQL Server 64-bit release, you can allocate four consecutive extents per given file, before switching to the next file in the filegroup. Lowering the frequency of file switches allows for larger contiguous ranges of disk space allocation, although it does not guarantee it, especially in the multiuser environment. Scans benefit from this contiguity because the data will be less fragmented allowing for fewer file switches and faster access. Additionally, the new allocation extension may decrease the number of I/Os executed during sequential scans. The performance increase is primarily expected for data warehouse applications with a limited number of users running index or data scans. You can activate the new allocation algorithm by using the -E option during SQL Server startup. For example:

sqlservr -E

The -E option must be used before the original data load because it does not affect existing data, only new allocations. Therefore, you must set the option every time the server is restarted for allocations to be affected. If not, the default file switch frequency is used during extent allocation. All operations that allocate space in a file, such as insert, index creation, and defragmentation, follow the new allocation algorithm when the -E option is defined. The -E option is designed to be used while loading and indexing a large database. Therefore, the size of I/Os during a future read operation are determined by how the server was configured at time of allocation, not by how the server is configured during the read.

Startup options can be set from SQL Server Enterprise Manager. For more information, see the "Startup Parameters" and "Using Startup Options" topics in SQL Server Books Online.

Because the -E option may bring performance benefits only under the specific circumstances described earlier, we do not recommend that you use this option on a regular basis. Running SQL Server with the -E option set on may decrease the performance of multiuser workloads with frequent allocations because of increased CPU consumption.