1

I've built a SQL 2014 cluster in our development environment that consists of 2 nodes. Non-Shared disks

Specifications:

  • Storage is Nimble Storage Array with dedicated LUN's presented to the hypervisors, each drive on each guest (data, logs, tempdb) has a dedicated LUN.

  • The LUN's are cached on SSD's and latency on the array and LUN's is under 0.25ms for both reads and writes.

  • Entire dataset of this test is < 350MB

  • vSphere 6.0U1

  • 2 identical VM's (Windows Server 2012R2 STD)

    • 10 vCPU
    • 64GB Ram
    • Storage is on dedicated VMDK's - pVSCSCi, all VMDKs are THICK-EAGERZERO.
    • VMXNET3
    • SQL Server 2014 SP1-CU5

  • AG name is AG02

  • Primary node is SQL2K14-02
  • Secondary node is SQL2K14-01
  • Synchronous mode
  • Automatic Failover

A copy of the production database showed 1000ms delay for inserts, so the same test has been run using HammerDB (www.hammerdb.com) in the following scenarios:

  1. HammerDB - Node1, no AG
  2. HammerDB - Node2, no AG
  3. HammerDB - AG1, Sync (Node1+Node2)
  4. HammerDB - AG1, Async (Node1+Node2)

Findings:

  1. The performance of test #1 and #2 are the same, so we know that we don't have a node that is slowing us down.
  2. Using AG1 as Sync has a Transaction Delayed time of 1000ms/sec which is very bad and we can't figure out why. HADR_SYNC_COMMIT wait time is all over the place.
  3. Using AG1 with Async we have 0 latency, but that is something we can't use.
  4. I was able to rule out networking latency as both VM's are on the same VLAN. Doing a constant ping + tcpping + iperf simultaneously showed latency of less then 0.3ms while both servers were pushing > 3Gbit/s over TCP.
  5. Below are graphs of the relevant metrics for AG.
  6. During the insert which is a single session running sequential insert commands (or update, or delete) I ran a trace on that session and saw that the duration was exactly the same as the graphs below.
  7. When running the same test without Sync (meaning Async, or directly on each node) I didn't see any latency, the duration for each operation was 0 (zero).

Question:

  • What is happening and why is there a whole second delay ?
  • I was thinking I'm doing something wrong, but didn't find anything in the documentation.

Thanks, Idan.

enter image description here enter image description here

Improve this question
5
  • blogs.msdn.microsoft.com/alwaysonpro/2015/03/03/… Notice the verbiage (LIKE ALTER INDEX). Not every workload fits well with synchronous commits in AG. Additionally, have you reviewed your VLF's for your db? Might be worth checking.
    – Andrew Loree
    Mar 23, 2016 at 15:25
  • @AndrewLoree thanks, the above metrics are from a new database I created for hammer DB, the database was created with 500MB for data and 300MB for logs to prevent auto-growth during the benchmark. VLF's are okay since it's a new database.
    – Idan
    Mar 23, 2016 at 16:19
  • Have you looked at HADR_SYNC_COMMIT vs WRITELOG wait ? Also, do you actually want your replicas in SYNC mode ONLY ? By anychance you have memory optimized tables defined in your AG ? What SP + CU you are using for your testing ?
    – Kin Shah
    Mar 23, 2016 at 16:47
  • @Kin I did look at those, but will look again while testing. Can you share the best method to measure these so I can monitor these while testing ? Regarding Sync, I must use Sync mode because we rely on automatic failure and we need the D in ACID. I'm using 2014SP1-CU5
    – Idan
    Mar 23, 2016 at 19:59
  • The larger question here is, "Where is the mirrored transactions/sec counter output?". The transaction delay counter is the total overall delay induced by all mirrored (synchronous) transactions per sec. Thus, you have 1,000 ms in transaction delay which must be divided by the number of total mirrored transactions/sec to get a final "ms of delay/transaction". Otherwise we have no idea how good or bad this is. It surely doesn't mean that each transaction took 1 second.
    – Sean Gallardy
    Apr 6, 2016 at 4:39

2 Answers 2

Reset to default
3

Turning my comment into an answer.

There seems to be some misunderstandings of how certain performance counters work when it comes to availability groups. Only Synchronous Commit replicas will be counted toward Transaction Delay (ms)/Sec Asynchronous Commit replicas will not change this counter as they transfer no mirrored transactions (yes, this could have been called something better).

Let me correct some of the assumptions in the thought process in the original question.

2.Using AG1 as Sync has a Transaction Delayed time of 1000ms/sec which is very bad and we can't figure out why. HADR_SYNC_COMMIT wait time is all over the place.

Transaction Delay (ms)/Sec, in a vacuum, means only that you have at least two synchronous commit replicas. This is because ONLY synchronous commit replicas have this value as we wait for the acknowledgements from the other synchronous replica that the data is hardened.

In order to have a complete picture and get an AVERAGE of how much latency overhead PER TRANSACTION we will need to capture the Mirrored Transactions/Sec counter. This counter will give us how many synchronous commit transactions were "mirrored" to other synchronous replicas. If we take the Transaction Delay (ms)/Sec and divide it by the Mirrored Transactions/Sec counter the end result will be the average delay in ms per transaction.

3.Using AG1 with Async we have 0 latency, but that is something we can't use.

This is not accurate at all. Ther, in fact, is GREATER latency is getting the transaction blocks hardened on the asynchronous commit replicas it's just that we don't wait for them to be hardened like we do with synchronous. Thus, there is still a delay (your network doesn't magically become a non-wait environment) it's just not counted in that specific counter as that counter ONLY contains information for synchronous commit replicas and transactions.

6.During the insert which is a single session running sequential insert commands (or update, or delete) I ran a trace on that session and saw that the duration was exactly the same as the graphs below.

There is not nearly enough information to make me believe this is the case. Even on some pretty terrible infrastructure I normally see 200-300 ms and nothing near 1 second PER individual transaction.

7.When running the same test without Sync (meaning Async, or directly on each node) I didn't see any latency, the duration for each operation was 0 (zero).

See my response to 3. which is also the same answer to this.

Follow Up

  1. Capture both counters, Transaction Delay (ms)/Sec AND Mirrored Transactions/Sec and post back the results.
  2. Test against your specific workload as the change in workload types will greatly factor in to response times.
Improve this answer
2
  • Thanks for the detailed response, I'm fully aware of these metrics and your information is valuable. I posted the actual answer in a separate answer. Thanks again!
    – Idan
    Apr 7, 2016 at 4:02
  • I also have scripts I developed to measure via T-SQL the above metrics you described. Both Instantaneous per instance and per-DB (via T-SQL perfmon counters). If someone would like me to share them, please feel free to ask and I'll share them.
    – Idan
    Apr 7, 2016 at 4:03
0

@sean-gallardy I'm fully aware of these metrics and that is what I was collecting. Nevertheless, the SQL cluster was choking. And took more than 1 second (times from the client application). After raising an issue with Microsoft (which did not help).

From the storage array there was no latency at all, so it wasn't the issue.

Finally, I decided to upgrade the vSphere version from 6.0U1 to 6.0U2 and that resolved the issue. While reviewing the vSphere change log, nothing was actually "fixed" that could have pointed me into that direction earlier.

All in all, thanks all for the support. If anyone ever encounters this behavior the actual drivers should be checked (physical or virtual).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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