Sql-server – Does SQL Server 2016 eliminate poor primary performance when reports take locks on seconday

1. I would only use RAID 0 for the logs if you're okay with having reduced disaster recovery capability. You won't be able to make log tail backups in the event of a disk failure, and will only be able to restore to your most recent backup. However, this may be less of an issue if you're running Availability Groups, particularly with synchronous mirroring. You generally won't have any performance overhead for RAID 1 vs. a single non-RAID disk, but you will have better I/O performance with RAID 0 instead of RAID 1 for an equal number of disks.

2. Be aware that tempdb also has both data files and log files, and those could end up interfering with each other (performance wise) if placed on the same volume. You may want to consider foregoing the spare disk and using it for tempdb logs instead if performance still isn't acceptable. If your C: array is large enough, you might be able to get away with using it for on-disk backups, though you should probably partition it to prevent runaway backups from filling your OS disk.

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.