When should startup parameter -E be used?

We are starting to build a data warehouse system using SQL Server 2008 R2. One of the senior DBAs told us to consider using a startup parameter -E for the SQL Server service. I read from Books Online that:


-E: Increases the number of extents that are allocated for each file in a filegroup. This option may be helpful for data warehouse applications that have a limited number of users running index or data scans. It should not be used in other applications because it might adversely affect performance. This option is not supported in 32-bit releases of SQL Server.

My question is how does this parameter help index and data scans? Should we use this parameter if there is a possibility that there are other databases (possibly in operative role) in the same instance?

The SQL Server Customer Advisory Team wrote a blog post that has some information on this setting here.

The –E startup flag

The SQL Server startup flag –E forces SQL Server to allocate 4 extents at a time to each file, essentially quadrupling the stripe size. In heavy insert scenarios, this drives larger block sizes to the disk. Also, your pages allocation will be more sequential with the same data file, allowing better sequential I/O for range and table scan operations (which are common in OLAP workloads).
This startup flag provides most, but not all, of the above mentioned benefits to I/O system – without the overhead of managing multiple filegroups. Be aware that this flag is supported only in 64-bit environments. You can find information about the –E startup flag in File allocation extension in SQL Server 2000 (64-bit) and SQL Server 2005 (KB329526).

StackExchange Database Administrators Q#29102, answer score: 6