AlwaysOn Log Send Queue Large

Question

AlwaysOn Log Send Queue Large

zlthomps

Default port

Points: 1480
More actions
November 30, 2015 at 5:58 am

#306678

Morning All-
I'm hoping this is the correct section for this post. I'm running into a little bit of a snag on high log send queue in a 2014 SP1 Asynchronous Mode AlwaysOn Environment with a secondary replica in Azure. For one of our higher transaction volume DB, we are seeing log send queue sizes up to nearly 2GB. Initially we saw a CPU bottleneck on the secondary due to the sizing of the Azure VM, which we corrected. However we were still left with some high log queue sizes and slow send rates, coupled with respective high re-dos.
We traced this back to an issue with IO using the follow error log message:
Message
There have been 112313856 misaligned log IOs which required falling back to synchronous IO. The current IO is on file...
This knowledge base article seems to address exactly what my issue is:
https://support.microsoft.com/en-us/kb/2936603
However its a year old and it last applies to CU5, which is about a year old, and we are about 8 patches past CU5. Additionally I cant find any official documentation from MS on trace flag 1800. Does anybody have an similar experiece with either this issue or traceflag 1800?
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

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

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 1

https://support.microsoft.com/en-us/kb/3009974 requires trace flag 1800 to be enabled. 18 hundred-level trace flags are likely to impact the storage engine (http://www.sqlservercentral.com/articles/trace+flags/70131/). I doubt tracestatus(1800,-1) will work (I suspect the storage engine has to be initialized with TF 1800 as a startup parameter). I assume trace flag 1800 reorders Windows API calls made by the storage engine (in sqlservr.exe). I suspect 1800 impacts handling the API call to fetch physical sector size and the API call to fetch the emulated sector size (so that SQL Server can quickly perform sector-aligned writes).

zlthomps Default port Points: 1480 More actions · Answer 2

Thanks for the info. I did DBCC TraceOn (1800,-1) with no affect and see continued misaligned IO errors. Ideally, Id want our disk sectors matching at 512e or 4k, but with the secondary in Azure and old SANs tech in the primary data center I've got issues. Was hoping this 1800 flag would help. Ill see about it as a start up parameter.

angelinemarren SSC Veteran Points: 293 More actions · Answer 3

Hi zlthomps

I know this is an old thread but I am interested to hear how you got on after enabling T1800 at startup?

Thanks

Angeline