Message misaligned log IOs which required falling back to synchronous IO in SQL Server Error Log
Summary
Disk Manufacturers introduced a new specification for disk storage formats in 2011. The new specification increases the size of the disk sectors. Some flash based devices like Fusion-io and new large capacity disks such as 4TB SATA disks are commonly using Native 4K format. The new disk format can lead to warnings in the SQL Server errorlog when log records are written in 512 or 512E format and restored on 4k Native format.
4k Native format can offer performance and reliability advantages in some circumstances. In most cases this error message is not serious, however the concepts and causes of this error should be understood.
1. Description of the Problem
The SQL Server Errorlog contains warning messages and Event ID 9012 is logged
Information 29-Apr-2014 3:17:24 PM MSSQL$PRD 9012 Server There have been 25958400 misaligned log IOs which required falling back to synchronous IO. The current IO is on file W:\MOUNTLOG\PRDLOG\PRDLOG1.ldf.
Information 29-Apr-2014 3:17:17 PM MSSQL$PRD 9012 Server There have been 25958144 misaligned log IOs which required falling back to synchronous IO. The current IO is on file W:\MOUNTLOG\PRDLOG\PRDLOG1.ldf.
2. Background?
First we need to understand some terminology
SQL Server is typically performing “Asynchronous IO” meaning IO operations are passing disk commands to the disk subsystem and then continuing to process other tasks. During a SORT operation SQL Server read ahead logic can read pages from the user database and then immediately start processing after the first page has been returned. This allows SQL Server to use the CPU resources to sort the rows on the page while the I/O subsystem is fetching (reading) additional pages. This increases the utilization of I/O bandwidth and other resources such as CPU.
SQL Server exposes the pending asynchronous I/O requests in the sys.dm_io_pending_io_requests DMV. The column io_pending_ms_ticks indicates how long the I/O has been pending. If the column reports FALSE for io_pending it indicates that the I/O has completed at the operating system level.
Asynchronous IO avoids “holding” a SQL Server worker thread in kernel mode and allows it to do other user mode processing.
Synchronous IO in comparison is calls to the API such as WriteFile that will not return control to the calling code until the operation is completed.
There are three Disk Sector Sizes: 4K Native, “Advanced Format” also called 512E that is downwards compatible with the older 512 byte format.
Only Windows Server 2012 or higher supports Native 4K format. Previous versions of Windows do not support Native 4K
“Bytes Per Sector" value |
"Bytes per Physical Sector" value |
Drive type |
4096 |
4096 |
4K native (Windows 2012 or higher) |
512 |
4096 |
Advanced Format (also known as 512E) |
512 |
512 |
512-byte native |
4K formats offer improved storage efficiency and better recoverability explained below.
3. How to Check Disk Sector Size?
The first step is to run the following command on all the disks where SQL Server datafiles, tempdb files or log files are stored. This should be run on all nodes in an AlwaysOn cluster, Log Shipping or SQL Mirror deployment.
fsutil fsinfo ntfsinfo <drive letter>
If this command is run on 4K Native disk the following output is seen. The Bytes per Sector = 4096
If this command is run on most conventional SAN disks the output below can seen. The Bytes per Sector = 512
512E Advanced Format looks like this - the Bytes per Physical Sector = 4096. This format is a 4096 physical sector size but a 512 logical sector size. This configuration is default on Hyper-V 3.0 VHDX disks.
If there are different disk sector formats in use there is a possibility that error 9012: There have been xxxx misaligned log IOs which required falling back to synchronous IO can occur
4. What Does This Message Mean?
SQL Server storage engine logic detects the disk Sector Size and will align the Transaction Log Files metadata and internal boundaries to match the Sector Size (either 512 or 4096 bytes). The error message 9012 is generated when SQL Server has detected that Log Entries have been written assuming a Sector Size different to the Sector Size found on the current SQL Server instance. This can happen in scenarios such as the below:
1. Log Shipping from a Production server with one disk sector size to a DR server with a larger sector size
2. AlwaysOn or Database Mirroring from a server with one disk sector size to a DR server with a larger sector size
3. When adding a 4K Native SSD card to hold the Transaction Log on a server where the Transaction Log was created conventional SAN disk with 512 sector size
To ensure consistency SQL Server may switch from performing asynchronous IO to synchronous IO. This can have an adverse performance impact if technologies like synchronous AlwaysOn or Mirroring are used.
In this scenario it is possible that log writes on an AlwaysOn replica or DBM secondary are switched to Synchronous IO. This could lead to higher than expected replication delays between the Primary and Replica(s)/Secondary
5. How to Modify the Sector Size?
FusionIO and other SSD vendors may provide utilities that allow configuration of Physical Sector Size. The screenshot below shows the FusionIO tool with a 4K Native format
The screenshot below shows a 512 Byte Physical Sector Size.
6. What are the Advantages of 4K Native Disks?
The diagram below shows the two key advantages of 4K Native Disks.
The first is improved storage efficiency. The diagram shows the storage efficiency improvement which is approximately equal to 512 Bytes per 4096 Bytes sector size.
The second advantage is improved distributed ECC checksums.
7. Is it Recommended to Reformat 4K Native Disks to 512/512E?
Each customer configuration can be different and there are a large number of possible deployment patterns. It is therefore difficult to give generic advice. 4K Native disks do offer advantages.
Some general comments:
1. It is generally recommended to have AlwaysOn or Database Mirroring configurations on hardware that is somewhat similar. The requirement is much less strict than SQL Server clustering. Similar hardware configurations between AlwaysOn nodes reduces the possibility of issues such as those discussed in this blog and also makes performance more predictable and easier to troubleshoot
2. Synchronous AlwaysOn or Mirroring scenarios might be adversely impacted by Replica(s)/secondaries switching to synchronous IO. In general it would be recommended to have the same sector size if Synchronous AlwaysOn or DBM is used
3. Log Shipping or Asynchronous AlwaysOn or Mirroring scenarios would typically not be adversely impacted by this warning message. It is possible that the restore of log records is somewhat slower at very high transaction volumes
8. What About Windows NTFS Format Size?
Any disk containing SQL Server datafiles, log files or tempdb files should always be formatted 64K at the Windows NTFS layer regardless of the underlying sector size.
To check this run command:
fsutil fsinfo ntfsinfo <drive letter>
The “Bytes per cluster” should be 65536. If this is not the case it is recommended to reformat the disk in Disk Management as per the screenshot below.
Links
https://msdn.microsoft.com/en-us/library/cc966412.aspx#EEAA
https://msdn.microsoft.com/en-us/library/cc966534.aspx
https://msdn.microsoft.com/en-us/library/windows/desktop/hh848035(v=vs.85).aspx
https://support.microsoft.com/kb/2510009
https://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx
Wikipedia https://en.wikipedia.org/wiki/Advanced_Format
https://technet.microsoft.com/en-us/sqlserver/aa365683(v=vs.100).aspx
Comments
- Anonymous
July 31, 2017
Hi ExpertsI need a help on the below query from my client, requesting you all please provide your suggestion on this-----------------------------------SAP ECC 6.0 Production system running on windows o/s with Database MS-SQL 2008 R2 release.The current block size is 4k and DBAs suggesting to go for 64K block size. DB size nearly 5 TB.What are the recommendations, pre-cautions and benefits to go with 64k from 4k block size.-------------------------------------------thankssatish- Anonymous
August 06, 2017
The recommended NTFS AUS is and has always been 64k
- Anonymous