November 28, 2017 at 1:04 pm
I'm looking at replacing 2-node SAN based failover clusters with AlwaysOn in syncronous mode and automatic failover. Many workloads perform sufficiently, but I have one application requirement which does lots of inserts in with relatievly small records counts iteratively. Basically, we create a "template" by inserting 100-1000 records into about 1000 tables... and we create 3000 templates at a time. Doing so takes about twice as long with syncronous replication as it does in async mode. Is this just the nature of the dual commit workload or is there anything I can do to tune for this? I can't find any resource contraint... network is not saturated, disks are not saturated, CPU does increase, but stays below 60% utlization.
June 21, 2018 at 7:52 am
Hi
With synchronous replication each Commit need to be hardened to the log of the secondary server before it can commit on the primary so each transaction incurs additional overhead. Things you can do to improve throughput without hardware changes
- If you are running lots of singleton inserts then try batching them or use a transaction to reduce the number of commits.
- If a small amount of data loss is acceptable then you can look at Delayed Durability. More info https://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply