Moved from: bobsql.com
When creating or growing the database log file (LDF) a byte pattern is stamped. The pattern establishes the proper log block layout with end of log marker(s.)
SQL Server 7.0 changed the LDF format from the original, 2K, Sybase database page design to sector aligned log blocks stamped with all zeros (0x00). Creation or expansion of the LDF file involves writing the entire series of 0x00’s to the new bytes for the log.
SQL Server 16 changes the stamp to 0xC0’s instead of 0x00s.
Note: The log file stamping pattern change has been leveraged by Window Azure SQL Database since calendar year 2014.
Question: If the log is stamped with 0xC0’s instead of 0x00’s how is it a performance gain?
Many of the new hardware implementations detect patterns of 0x00’s. The space is acquired and zero’s written to stable media, then a background, hardware based garbage collector reclaims the blocks.
SQL Server needs to expand or create a new segment in the LDF to support active, transaction activity.
- The region is allocated
- Stamped with the 0x00’s pattern
- Hardware starts to reclaim the newly allocated regions
- SQL Server needs to write new transaction log records
The hardware, reclamation can force the new transaction log records to repeat the acquisition of space.
Another factor with reclamation activities is the interruption of asynchronous I/O. SQL Server is designed to post (hand off the I/O asynchronously) and allow the worker to continue processing. Reclamation often results in the I/O request getting completed in a synchronous fashion. This pattern disrupts the expected scheduling behavior, reducing SQL Server performance.
Simply stated, acquiring, releasing and acquiring again is suboptimal. The re-acquire activities impact performance and open the door for unexpected failure points. As an example, SQL Server grew and stamped the log but when SQL Server attempts to write the log record the hardware may no longer have space. When SQL Server can no longer write log records all transaction activity for the database is halted.
‘It Just Runs Faster’ – Changing the pattern to 0xC0’s avoids common reclamation techniques, improving performance.
Warning: Carefully review the performance impact of thin provisioning and de-duplication subsystems used to store SQL Server database files.
Jul 2016 Update
Question: Does the stamping apply to database files?
Answer: No
Data files remain stamped with the 0x0 (zero) pattern. One could argue the benefits of 0xC0 apply as well to data files but if you look closer the zeroed image contract is also important. When you create snapshot databases (or dbcc replicas) the files are marked sparse and the contract of a sparse file is to return zero’s for unallocated space. When you use Instance File Initialization (IFI) the contract includes zeroed page images. Looking to the future, non-volatile storage solutions could appear with direct memory access patterns that typically expect zeroed images.
The performance impact of zeroed images is reduced for database files because the writes to databases files happen out-of-line. Meaning a commit requires the log file write to complete, supporting WAL and crash recovery needs while the command waits. The database page writes happen via checkpoint and lazy writes, using asynchronous patterns instead of inline with the command.
Posted at https://sl.advdat.com/3vqmPDihttps://sl.advdat.com/3vqmPDi