SQLServer 2016 AlwaysOn and new Log Transport behavior

  • Article

Hello, my name is Simone Greci and I'm an Italian Premier Field Engineer on SQLServer. My first post is about new AlwaysOn log transport behavior and related trace flags. Let's dig deeper.

A customer of mine request me to understand how much is transaction log transport compression ratio for the new SQLServer 2016, useful information if you want to size correctly your network between two replicas. Consider the following test environment:

  • SQLServer 2016 RTM on Windows Server 2012 R2 (local Hyper-V lab).
  • Availability Group configured in Synchronous Replication, Automatic Failover and Automatic Seeding.

I added a database to the Availability Group and made some tests with HammerDB, an open source benchmark utility to simulate a consistent read/write workload based on TPC-C standard.

During the test I registered on PerfMon the following performance counter objects on the primary replica:

  • Databases\Log Bytes Flushed/sec
  • Availability Replica\Bytes Sent to Replica/sec

In my environment with SQLServer 2016 what I can see is that Bytes Sent to Replica/sec is basically the same as Log Bytes Flushed/sec, looks like no compression is done:

pic1

But according to this MSDN Blog article (actually for SQLServer 2012):

“... besides encrypting the data which goes over the network to secondary replicas, SQL server also compress the data sent to the replicas in order to reduce network bandwidth requirements. In our example case we saw the following values:

  • Bytes Sent to Replica/sec for one specific replica: 850KB/sec
  • Log Bytes Flushed/sec for the only database in the AG: 1950KB/sec
  • Means all in all we are looking into a compression rate of around 2.3”

The same test with HammerDB utility has been done also on SQLServer 2014 SP1 (12.0.4100.1) on Windows Server 2012 R2 and I got expected results:

pic2

With SQLServer 2014 compression is done on the log transport, Log Bytes flushed to transaction log are twice the Bytes sent to replicas. But why not on the new SQLServer version?

The reason for this difference is that defaults for AlwaysOn log transport compression are changed on SQLServer 2016:

  1. Log stream compression is disabled by default for Sync replicas to ensure sync replication does not slow down OLTP relative to standalone. Compression adds latency, hence off by default. Flip it by using -T9592.
  2. Log stream compression is enabled by default for Async replicas to reduce ingress\egress costs for geo replication so compression is enabled by default for async. Flip it by using -T1462.
  3. Compression is disabled by default for Automatic Seeding to reduce CPU usage on primary. Flip it by using -T9567.

New trace flags will be on MSDN soon, stay tuned.