512e (Advanced Format) Drives

  • dave-L

    SSCommitted

    Points: 1516

    I have an 2 node, AlwaysOn deployment on Windows 2012 R2 and SQL Server 2012 SP2. Both replicas are on 512b sector size drives.

    We are migrating to a new VPC in AWS. In doing this we have added a replica to the availability group that is in AWS. This replica was created using SSD volumes in AWS, which are reporting themselves as 512e (512b sector size / 4096 physical sector size).

    Now that this 3rd, AWS replica is in the availability group, I'm getting errors in my SQL log like:

    There have been X misaligned log IOs which required falling back to synchronous IO.

    I gather this is because the primary replica's log file is on a 512 byte sector size drive and the new replica's log file is on the 512e (512/4096) drive.

    I have two questions about this I can't seem to find an answer to:

    (1) Once we move completely over to the new VPC, and all drives are 512e, will this error message stop?

    (2) Is there a performance issue having SQL server on 512e (Advanced Format) drives?

    Any insight would be appreciated.

    Thanks.

    As a side note:

    If I create the partitions on the AWS replica using Disk Manager the resulting volumes report 512b sector / 512b physical sector. If I create the partitions using Storage Pools the resulting partitions report 512b sector / 4096 physical sector.

    I read that *all* SSDs have a physical 4k sector, so I'm assuming Disk Manager is simply creating the partitions unaware of the 512/4096 translation and the Storage Pools are aware of this; either way I think the translation is there.

  • SoHelpMeCodd

    SSCertifiable

    Points: 5870

    There is a bug fix - see https://support.microsoft.com/en-us/kb/3009974. What does SELECT @@version return? SP2 CU3 is 2011.110.5556.0. Workaround would be to ensure sector sizes match

  • dave-L

    SSCommitted

    Points: 1516

    My version is 11.0.5623 (SQL2012 SP2 CU7), so it looks like I have that hotfix already, which might be is why I'm not seeing any performance issues at this time, just lots of errors in the SQL Error Log.

    I noticed the hot fix talks specifically about sector size mismatches from 512b to 4k. I wonder if this includes the Advanced Format where the drive is emulating 512b but actually using 4k, which is what I have.

  • SoHelpMeCodd

    SSCertifiable

    Points: 5870

    Did you turn on trace flag 1800?

  • dave-L

    SSCommitted

    Points: 1516

    Good call, I hadn't enabled the trace flag. I've done this on the secondary (with no affect). I'll enable the trace flag on the primary during the next maintenance window and report back on the results.

    Thanks you.

  • dave-L

    SSCommitted

    Points: 1516

    I've enabled trace flag 1800 on all replicas now and I'm still receiving this error. I did not restart the SQL service though. I place the trace flag in the start up parameters and issues a DBCC TRACEON (1800, -1) command. I believe this should do it. Would you agree that restarting the service should not be necessary?

  • SQLQuest29

    SSCrazy Eights

    Points: 8264

    dave-L (11/24/2015)


    I've enabled trace flag 1800 on all replicas now and I'm still receiving this error. I did not restart the SQL service though. I place the trace flag in the start up parameters and issues a DBCC TRACEON (1800, -1) command. I believe this should do it. Would you agree that restarting the service should not be necessary?

    You have to restart sql server when you put the TF 1800 as startup parameter.

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • angelinemarren

    SSC Veteran

    Points: 293

    H Dave-L

    how did you get on when you restarted the service? I know this is an old thread but would be useful to know if it fixed the issue and if you experienced any negative impacts of enabling T1800.

    Thanks

    Angeline

  • dave-L

    SSCommitted

    Points: 1516

    Angeline,

    I sure do wish I had updated this thread after. 🙁

    I honestly can not recall if the restart solved the problem. I did manage to find some notes I still have on the migration and I make no more mention of the issue after my last post, and in fact I found notes detailing that I had started using the secondary as a read-only replica. This would seem to indicate the issue was solved.

    I wish I could say more 🙁

  • angelinemarren

    SSC Veteran

    Points: 293

    No worries, I knew it was a long shot to ask if you remember restarting a server 5 years ago ??

    Thanks for the reply anyway.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply