512e (Advanced Format) Drives

Question

512e (Advanced Format) Drives

David Lafayette

SSCommitted

Points: 1559
More actions
November 23, 2015 at 6:40 pm

#306521

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.

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

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

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 1

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

David Lafayette SSCommitted Points: 1559 More actions · Answer 2

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: 5871 More actions · Answer 3

SoHelpMeCodd

SSCertifiable

Points: 5871

November 24, 2015 at 12:19 am

#1841925

Did you turn on trace flag 1800?

David Lafayette SSCommitted Points: 1559 More actions · Answer 4

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.

David Lafayette SSCommitted Points: 1559 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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

David Lafayette SSCommitted Points: 1559 More actions · Answer 8

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 More actions · Answer 9

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.