This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
Today, I worked on a service request that our customer faced a performance issue in their business critical service tier database. Their main suspect was that the syncronization with the secondary replica has a delay.
During their troubleshooting process they found that they got many request with the wait type is HADR_SYNC_COMMIT. This wait type indicates the time that the primary replica spends waiting for the sconedary replica to harden the log records (LSN). This wait type only occurs on the primary replica and only inside a synchronous replication that we could have for every internal replica when we created a business critical or premium service tier database.
As soon as a transaction is received in the primary replica and it sends to the secondary replica for hardering, the wait type HADR_SYNC_COMMIT wait time starts counting the time until receiving the confirmation for the secondary's transaction log was completed.
Following, I would like to share with you how is possible to reproduce this and how is posible to know the latency.
I created this table including the option OPTIMIZE_FOR_SEQUENTIAL_KEY=ON for improvements when we are adding incremental values.
In order to reproduce a high number of transactions, I developed the following store procedure:
Finally, using the tool oStress, we are going to run 798 concurrent process (my business critical has 8 vCores and 800 workers limit) the store procedure 10000 times.
Running this process we could see the first HADR_SYNC_COMMIT using the following query:
For every execution we could see, in the following image, that gave us, as an example, the latency per request.
Also, using the following query:
The Waiting Task Count column give us the number of single transactions done until now, if we divide by wait_time_ms migh give us the milliseconds in average that took for syncronization for secondary. In this case, 676004/372743 = 1.8 milliseconds.
Finally, our customer asked if could be possible to have information about CPU, IO and workers used. We replied that using the query SELECT * FROM SYS.DM_DB_RESOURCE_STATS you could have the following information:
Enjoy!