I've built a SQL 2014 cluster in our development environment that consists of 2 nodes. Non-Shared disks
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
2 identical VM's (Windows Server 2012R2 STD)
- 10 vCPU
- 64GB Ram
- Storage is on dedicated VMDK's - pVSCSCi, all VMDKs are THICK-EAGERZERO.
- 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:
- HammerDB - Node1, no AG
- HammerDB - Node2, no AG
- HammerDB - AG1, Sync (Node1+Node2)
- HammerDB - AG1, Async (Node1+Node2)
- 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.
- 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.
- Using AG1 with Async we have 0 latency, but that is something we can't use.
- 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.
- Below are graphs of the relevant metrics for AG.
- 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.
- 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).
- 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.