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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Has anybody seen evidence of the effect of -E when using SSDs?

The effect on 'spinning rust' drives is not disputed - but SSDs aren't really flustered by random I/O. I wonder if the -E option might even hurt performance.

On servers with a mix of drives (SSD SAN, PCI SSDs and traditional SAN) SQL Server has to decide on startup whether to use -E or not. I have some empirical evidence that the option might be detrimental to performance, but I'd like other people's feedback before I consider taking it off.

My setup uses standard 64K RAID stripes, and the NTFS cluster size is also 64K.

share|improve this question

The following articles explore the advantages and disadvantages of the -E start-up option:

Focus on Fast Track : Understanding the –E Startup Parameter


-E changes the proportional fill algorithm. Rather than moving on to the next file after a single extent allocation, the movement occurs after 64 extents have been allocated.

These extent allocations do not need to come from a single table. For example, when loading two tables in parallel with –E enabled, both tables consume the 64 extents in the single file before moving on to the next file. -E does not alter the fact that SQL Server allocates one extent at a time.

The purpose of –E is to enable large-I/O read-ahead. If two tables that share the same files/filegroup are loaded in parallel then the advantage of –E is removed. The extents will be fragmented resulting in small-I/O read-ahead.

-E was originally put into the code to allow index builds and rebuilds to use 256KB of each file before round-robining, and so get better sequential I/O for large data warehouses. It was originally a 'benchmark special' and had very little testing, it was only documented because it was used during benchmarks (and so had to be documented).

Microsoft White Paper (pdf)

Community Wiki answer generated from a comment on the question by @Kin

share|improve this answer

Your Answer


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.