Udostępnij za pośrednictwem

SQL 2016 - It Just Runs Faster: LDF Stamped

  • Artykuł

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.

 

Peter Byrne - Principle SQL Server Software Engineer

Bob Dorr - Principal SQL Server Escalation Engineer

Comments

  • Anonymous
    March 22, 2016
    Thanks for this information and change. I was wondering if you'd been able to quantify the performance gain in your tests?
  • Anonymous
    March 23, 2016
    Does this same behavior apply to data files when Instant File Initialization is not enabled?