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.

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
    
What version of SQL Server are you using ? –  Kin Jun 3 '13 at 13:55
    
I suspect there will only be a performance benefit if the RAID stripe size is relatively large. If SQL goes to allocate, say, 8 extents (512K) at once and the RAID stripe is only 128K, that's going to result in multiple physical I/Os per logical allocation. I assume (but haven't tested) that will always be slower than a single physical I/O even on SSDs. –  Jon Seigel Jun 3 '13 at 15:31
    
SQL versions 2008 and R2. Raid stripes standard at 64K, NTFS cluster size also 64K –  John Alan Jun 3 '13 at 17:20
1  
@JohnAlan Read up this article for thorough understanding of "-E" parameter -- consultingblogs.emc.com/jamesrowlandjones/archive/2010/04/25/… and this whitepaper from Microsoft research.microsoft.com/en-us/um/people/antr/ms/ssd.pdf –  Kin Jun 4 '13 at 21:48
    
Both links are excellent and relevant - Thanks Kin –  John Alan Jun 5 '13 at 6:51

1 Answer 1

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

Focus on Fast Track : Understanding the –E Startup Parameter

Summary:

-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

 
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.