SQL Server uses
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 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 (
) increase the number of contiguous, 8K pages from 32 to
(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.