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

Moved from: bobsql.com


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