SQL 2016 - It Just Runs Faster: Larger Data File Writes

SQL Server uses WriteFileGather for the vast majority of data file write requests. The logic is to consolidate dirty pages into a single I/O request.

 

For example page 1:13 and 1:12 are dirty resulting in a single WriteFileGather operation.

 

 

 

Is BUF[1] Dirty - Yes - Write with gather near

Do hash lookups until we have 32 pages (SQL Server 2012 and 2014) or no more dirty pages to create a contiguous block.

 

Is Page 1:14 in memory (hashed) and dirty NO - End forward search for near pages

Is Page 1:12 in memory (hashed) and dirty YES - Include in write request

Is Page 1:11 in memory (hashed) and dirty NO - End of backward search for near pages

 

Write pages 1:12 and 1:13 in a single (WriteFileGather) block

 

For over a decade hardware performed best with at 64 or 128K requests. The newer SSD and flash implementations often have 1MB to 4MB internal blocking sizes. Increasing the overall size of data file writes slim lines the (R)ead (M)odify (W)rite behavior, increasing scalability and performance.

 

SQL Server 2016 (X64 installations) increase the number of contiguous, 8K pages from 32 to 128 (1MB) when performing (Lazy, checkpoint, select into, create index and bulk insert write operations.) These write operations encompass 95%+ of the write operations for data file.

 

'It Just Runs Faster' - SQL Server 2016 takes advantage of newer hardware scalability by increasing the database file write operations.

 

Bob Dorr - Principal SQL Server Software Engineer