AlwaysOn Log Send Queue Large

  • zlthomps

    Default port

    Points: 1480

    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!

  • SoHelpMeCodd

    SSCertifiable

    Points: 5870

    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

    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

    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

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

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