Update: This post is participating in the 4th installment of the T-SQL Tuesday hosted this time by Mike Walsh
.SQL Server 2005 came few years ago with lots of optimizations and in this post I will add some details on one of the lesser known IO enhancement that wasn’t present in previous versions.
CHECKSUM was introduced as a PAGE_VERIFY option starting from SQL Server 2005 and the two widely known options are PAGE CHECKSUM and BACKUP CHECKSUM. The other kind and the lesser known option is log block CHECKSUM.
What is a log block?
The smallest unit of data storage in SQL Server is a page which is 8 kb in size. SQL Server manages space as Extents, which are made of eight contiguous pages. When it comes to Transaction Log, the fundamental unit of IO is a log block. The size of the log block is *NOT* fixed and it varies from 512 bytes to 60 kb. Virtual log files known as VLFs are made up of log blocks. And the Transaction Log is made up many VLFs, which are variable in size. For more details on the log block, refer Paul’s post listed below.
How do I turn on log block CHECKSUM?
PAGE CHECKSUMs are enabled using the ALTER DATABASE PAGE_VERIFY option. Log block CHECKSUMs are enabled automatically when the CHECKSUM option is set for PAGE_VERIFY. Note that turning on TORN_PAGE_DETECTION has no effect on the log block and is NOT applicable.
How does log block CHECKSUM works?
When SQL Server commits a transaction and issues a write for the transaction log, just before the log block is written to disk the CHECKSUM over the log block is computed and stamped on the header of the log block very similar in nature to the data page. When the transaction log is read during the backup and restore (and other activities listed in Paul’s post below), the CHECKSUM value is re-computed and checked against the previously written value in the header.
How does log block CHECKSUM help?
If the CHECKSUM value of the log block doesn’t match with the existing CHECKSUM value then IO subsystem has compromised your data. One has to check and validate the IO Subsystem extensively to find the root cause of the problem.
Does Transact SQL and PAGE/BACKUP/Log Block CHECKSUM use the same algorithm?
No. Transact SQL CHECKSUM algorithm is different from the PAGE/BACKUP/Log Block CHECKSUM, which uses simple a yet efficient algorithm compared to T-SQL CHECKSUM.
Popular posts