Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.