SQL Server - New Drives Use 4K Sector Size

In a lengthy discussion this past week I was reminded that Jan 2011 is when the hard drive manufactures agreed to focus on drives with sector sizes of 4K. I have read all the latest materials about this over the past week and you can too. Just search for 512e or Advanced Format Sector sizes and you will find the same articles I read. I concentrated on articles by Seagate, Western Digital and other manufactures.

Why am I talking about this on a SQL Server blog? - The change has impact to your SQL Servers. There are two areas you need to be aware of. PERFORMANCE and DATA INTEGRITY

PERFORMANCE: All the articles outline the performance implications for the 512e (512 byte sector size emulation mode). This is important to you because when SQL Server creates a database it makes the Windows API calls to determine the sector size. When 512e is enabled the operating system reports 512 bytes and SQL Server aligns the log file I/O requests on 512 byte boundaries. This means that placing a database on a 512e enabled drive will cause SQL Server to engage the RMW (Read-Modify-Write) behavior and you could see elongated I/O times when writing log records. This many only be a millisecond or two but can accumulate quickly.

DATA INTEGRITY: When I point this out I am not indicating that the 4K sector based drives are inherently any better or worse than 512 byte sector drives. In fact, many of the designs for the 4K sector drives allow an enhanced ECC mechanism so in some respects the drives could be considered more resilient to media failure conditions than the 512 byte sector formats.  

What I am warning about is the Read-Modify-Write behavior that takes place under the 512e mode. When SQL Server thinks the drive is handling 512 byte sectors the log I/O is aligned on 512 byte boundaries so a partial 4K write could be encountered at the drive level. Some specifications say that the drive may bundle these until the 4K sector is filled before flushing to the platter media, others are not so detailed in their information. If the drive holds the 512 byte write in disk cache (not battery backed) but reports the write complete to SQL Server, SQL Server can flush the data page because it thinks it has met the WAL protocol requirement for writing the log record before the data page. If a crash occurs at this point and the disk cache does not have time to flush you have missing log records that recovery won't know about.

SNIPPETS

Here are a few snippets from the articles I read.

A drawback to the current r/m/w operation is that a power loss during the r/m/w operation can cause unrecoverable data loss. This possibility occurs during every r/m/w operation, at the point where the two part-modified sectors at the start and end of the logical blocks (i.e., the "boundary" sectors) are being written to the media.

In modern computing applications, data such as documents, pictures and video streams are much larger than 512 bytes. Therefore, hard drives can store these write requests in cache until there are enough sequential 512-byte blocks to build a 4K sector.

Read-Modify-Write Prevention  

As described above, a read-modify-write condition occurs when the hard drive is

issued a write command for a block of data that is smaller, or misaligned, to the

4K sectors. These write requests are called runtssince they result in a request

smaller than 4K. There are two primary root causes for runts in 512-byte emulation.
 

1. Write requests that are misaligned because of logical to physical partition misalignment

2. Write requests smaller than 4K in size

RECOMMENDATION

For SQL Server the best recommendation is to work with the hardware manufacture to make sure the 512e mode is disabled on drives that hold the SQL Server database and log files and that the Windows API is reporting 4K sector sizes. SQL Server will then align the log writes on 4K boundaries and avoid the emulation behavior.

May 2011 Update - At the current time Microsoft is not aware of a drive that provides an option or jumper to disable the 512e mode. Some manufactures indicate this might be possible in the future so you should discuss this aspect with the hard drive manufacture.

SUPPORT BOUNDARIES

Make sure you are using a supported operation system version for 4K sector drives: https://support.microsoft.com/kb/2510009 and https://support.microsoft.com/kb/982018

Drives that support 512e logical mode using physical 4K sector sizes are supported on currently shipping operating system. Drives that support 4K logical and 4K physical sector sizes are not currently supported by Windows. Check the support boundaries in future versions of Windows for possible support capabilities.

SQL Server 2008 R2 PCU 1 with the associated Windows QFEs support activity against 512e drives because the QFEs allow SQL Server to determine the proper physical sector size and align on the physical size properly.

MOVING DATABASES

SQL Server does validate the sector sizes are valid to support SQL Server database pages and log buffers. If an invalid sector size is encountered you may encounter errors such as 5178, 3268, 3269 or similar messages. If you receive these messages use the (new) FSUTIL and MSINFO utilities to review the sector size information.

Bob Dorr - Principal SQL Server Escalation Engineer

Comments

• Anonymous
  January 13, 2011
  When you say "may prevent you from attaching or restoring the database to a drive of different sector size.", what kind of messages or errors would we get?

• Anonymous
  January 13, 2011
  When talking about checking the sector size through the Windows API, are you referring to the GetDiskFreeSpace function?

• Anonymous
  January 16, 2011
  "... may prevent you from attaching or restoring the database to a drive of different sector size"What? So if our old hardware dies and we can't get an identical drive, our backups are useless? Surely that can't be right.

• Anonymous
  January 18, 2011
  Answering commentsThe API is DeviceIoControl  - Disk Management Control Codes - msdn.microsoft.com/.../aa363979(v=VS.85).aspxErrorNumber: 3269ErrorFormat: Cannot restore the file '%ls' because it was originally written with sector size %d; '%ls' is now on a device with sector size %d.The check is not a simple size of sector match check.    // The sector size used when the source was formatted must be a multiple of// any target device onto which we want to restore.

• Anonymous
  February 07, 2011
  As always I got some good feedback from my international peers.To clarify I am talking about the 'physical disk sector size' and not the NTFS cluster size when formatting the drive.

• Anonymous
  April 03, 2013
  Is there a utility like sqlio, or switch for sqlio that allows us to simulate SQL I/O?

• Anonymous
  January 22, 2015
  Hi!thx for good post.Is there any  changes in 4 k native support?As I understood from here msdn.microsoft.com/.../hh848035(v=vs.85).aspx4 k disks are fully support in windows server 2012 r2.I have SAN with 4k format disks on board.But I see in my  fsutil  that WS using 512b (with RMW).NTFS Volume Serial Number :      0x888c46038c45ec6eNTFS Version   :                  3.1LFS Version    :                  2.0Number Sectors :                  0x0000000700003fffTotal Clusters :                  0x00000000e00007ffFree Clusters  :                  0x000000008e615138Total Reserved :                  0x0000000000000000Bytes Per Sector  :                   512Bytes Per Physical Sector :       4096Bytes Per Cluster :              4096Bytes Per FileRecord Segment    : 1024Clusters Per FileRecord Segment : 0Mft Valid Data Length :          0x0000000000040000Mft Start Lcn  :                  0x00000000000c0000Mft2 Start Lcn :                  0x0000000000000002Mft Zone Start :                  0x00000000000c0040Mft Zone End   :                  0x00000000000cc840Resource Manager Identifier :     B6D6AEF2-8184-11E4-80C7-0025B5040B00How  I could change it to 4k?(for reducing hidden cost for read-modify-write) What I need to do?

• Anonymous
  June 17, 2015
  so...as I read this, SQL Server doesn't support 512e mode, and none of the new hard drive providers allow you to turn off 512e mode so that SQL Server functions....so an upgrade with a hdd that is advanced format breaks SQL server...what am I to do with my applications that use SQL Server? Wait until either I or my non-advanced format hdd dies?

• Anonymous
  July 02, 2015
  Any updates on if a drive provider allows 512e to be disabled? I have a legacy SQL Server application that I cannot move and it will not run on an advanced format drive...

  • Anonymous
    May 11, 2016
    @Bob are you on a VM / Physical?
  • Anonymous
    September 07, 2016
    I faced the same error messages with SQL Server replacing both the faulty and the healthy drive of a raid 1 system with 4kn drives. Even our system support team didn't have any information about the problems associated with the physical sector sizes. I realized them by accident. As a workaround I ordered two 512n drives to migrate the raid, both with 1TB capacity. I will report if it worked.
  • Anonymous
    September 09, 2016
    Well, it worked. Just put an image of the old system to a WD RE 512n 1TB drive for raid migration. 512n drives seem to be the most reliable and practical solution at the moment.