3

We have a weird issue while we setup the AlwaysOn on Azure VM. We have explicit transactions in our stored procedures, and which are causing some delays and blocking when till the time we get the response from Secondary Server. We are in Synchronous commit mode of AlwaysOn to achieve High-Availability. But it seems we are seeing issues there as when we do the explicit commit the transactions are taking lot of time to complete and causing blockings. The transaction generaly gets stuck at "HADR_SYNC_COMMIT" wait type.

When we removed the explicit transactions from the stored procedures, the execution of the stored procedures was pretty faster and there were no blockings post that.

Anyone aware of any specifics about behaviour of AlwaysOn on Azure VM

Thank you.

0

Got all the answers to my question mentioned above. If anyone facing the same problem I would like to point them to the answer : Here's the answer to all the questions why explicit commit takes time :

[A commit transaction (FlushToLSN/StartLogFlush) can trigger similar behavior, even when the block is not full, so a commit transaction does not have to wait for the block to become full.]

Please find more details on following link : https://support.microsoft.com/en-gb/kb/230785]

Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.