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