This blog outlines a new twist to my previous blog outlining issues with 4K sector sizes.
SQL Server - New Drives Use 4K Sector Size: http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx...
In the previous post I discussed that it was unsafe for the I/O subsystem to present a sector size that was smaller than the actual, physical sector size. This leads to unsupported, Read-Modify-Write (RMW) behavior.
I was doing testing on a Windows 2012 Server - Storage Space setup and found that both Storage Spaces and the VHDx format can report a 4K sector size to the SQL Server. This allows the various drives setup in the pool for Storage Spaces to be of disparate sector sizes (Drive 1 = 512 bytes, 1K, 2K, and Drive 4 = 4K.)
Is this safe for SQL Server?
The answer is yes . An I/O subsystem can return a larger sector size than actual, physical sector size as long as all reported values can be evenly divided by 512 bytes.
As the diagram below shows, SQL Server maintains parity on 512 byte boundaries, for the log, regardless of the reported sector size. This allows SQL Server to detect a partial write (torn behavior.) For example, if the system reported a sector size of 4K but the physical sector size was 512 bytes, the I/O subsystem is only guaranteed to flush to a 512 byte mark . If the first 4, physical sectors are flushed (2K of the 4K aligned block) and a power outage occurs, SQL Server will be able to detect the entire 4K was not properly flushed.
Without the logical parity every 512 bytes SQL Server would be unable to detect the torn situation, leading to unexpected recovery and logging behavior(s).
WARNING: While SQL Server protects your data against such a failure the reporting of sector size, larger than physical sector size, can lead to unwanted/unexpected space usage. SQL Server will align the log writes to the reported sector size (4K in this example.)
SQL Server packs records within the log blocks and then aligns/pads the writes on the reported sector boundary. Lots of small transactions, leading to many log flushes, can result in wasted log space for a system reporting larger sector sizes. Moving the scenario to an I/O subsystem reporting smaller sector sizes can reduce space usage.
The easiest way to see this in action is a single worker doing tiny transactions.
while(1=1)
begin
insert into tblTest values (1) // Each insert is a transaction and a log flush
end
Each insert is a separate commit transaction, causing the log to be flushed for each iteration. In this example each insert will require at least 4K of log space to properly align during the flush. Wrapping a transaction around the while loop or only committing at reasonable boundaries (say 10,000 inserts) reduces the log flushing behavior and uses the log space more effectively.
Bob Dorr - Principal SQL Server Escalation Engineer