(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Last week I have talked about the basic concepts behind Automatic Seeding in SQL Server 2016 Availability Groups. In today’s blog posting I want to continue by talking about Compression optimizations available in combination with Automatic Seeding.

How do I enable Compression?

If you are using Automatic Seeding when you add replicas or new databases into your Availability Group, SQL Server doesn’t use compression optimizations by default – they are just disabled! With this approach Microsoft wants to make sure to introduce as less CPU overhead as possible during the seeding operation. But of course, the more data you are transferring (uncompressed) through your network, the longer the operation takes.

To enable compression during Automatic Seeding operations you have to enable Trace Flag 9567. The following picture shows you how you can enable that Trace Flag through the SQL Server Configuration Manager on an instance level.

After you restart SQL Server, the Automatic Seeding operations will be performed with compression.

How fast does it get?

To give you an idea how fast Automatic Seeding operations can be with compression enabled, I have done a quick comparison in my home lab (based on physical vSphere hosts). I have seeded a 32 GB large database from the primary replica to a newly added secondary replica. Both replicas were running on 2 different vSphere ESXi hosts connected through a 10Gbit network connection. Unfortunately I have my SATA 6 based SSDs attached on an (old) SATA 3 interface, and therefore the primary replica was only able to read about 250 MB of source data per second from the storage subsystem.

And therefore I was of course not able to saturate the 10Gib network link between both replicas. The following 2 pictures shows the CPU and network utilization on the primary and the secondary replica – without using compression (the CPU times are higher on the secondary replica, because I was running it with only 2 vCPUs – compared to 4 vCPUs on the primary replica).

As you can see from the X axes, the whole seeding operation took around 5 minutes (21:37pm – 21:42pm). Afterwards I removed the secondary replica from the Availability Group, and I have enabled Trace Flag 9567 as mentioned previously on the instance level. Here are now 2 other pictures with the CPU and network utilization for the same seeding operation again – with compression enabled.

The difference is tremendously: the whole seeding operation of the 32 GB large database finished within 90 seconds – compared to almost 5 minutes this is a huge difference! The average CPU utilization was about 33% compared to 27% without using compression. Here you can see quite nicely that compression introduces a CPU overhead that you have to investigate for your concrete scenario.

Summary

If you have CPU headroom available (which should be normally the case), you should (at least) test if compression helps you to improve the throughput of Automatic Seeding operations. The less data you are transferring through your network, the faster the operation will be.

In addition to Trace Flag 9567, SQL Server also offers you the Trace Flags 1462 and 9592 with which you can control if compression is performed for synchronous (uncompressed by default) and asynchronous (compressed by default) replicas when they synchronize Transaction Log records. Microsoft gives you in the article Tune Compression for Availability Groups more information about these other Trace Flags.

Thanks for your time,

-Klaus