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.
Best Answer
The following articles explore the advantages and disadvantages of the
-Estart-up option:Focus on Fast Track : Understanding the –E Startup Parameter
Summary:
-Echanges 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
–Eenabled, both tables consume the 64 extents in the single file before moving on to the next file.-Edoes not alter the fact that SQL Server allocates one extent at a time.The purpose of
–Eis to enable large-I/O read-ahead. If two tables that share the same files/filegroup are loaded in parallel then the advantage of–Eis removed. The extents will be fragmented resulting in small-I/O read-ahead.-Ewas 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