Condividi tramite

SQL Server–Storage Spaces/VHDx and 4K Sector Size

  • Articolo

This blog outlines a new twist to my previous blog outlining issues with 4K sector sizes.

SQL Server - New Drives Use 4K Sector Size: https://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.

 

image

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

Comments

  • Anonymous
    January 20, 2014
    I'm still a little confused.   We just hit the situation where we distribute databases from a build server out to remote locations.   The build server was recently upgraded to use SSD with 512 sector sizes on all drives.    At the destination site they were able to restore the databases on devices that were 4096 sector size but if they tried to back up that database back to the initial backup file (with a NOFORMAT option) it failed with the error about the sector size; can't restore 4096    Using FORMAT or specifying the BLOCKSIZE=4096 solves the problem but I want to make sure it is not covering up a more important problem. When a database is created and backed up on a system with all 512 sector size devices... what are the impacts of restoring this database to a server with all 4096 sector size devices including the log file.   My concern is around the boundaries that SQL Server thinks it is dealing with.    What is the best practice for dealing with this situation? Thanks for any feedback. Don