Tune compression for availability group

THIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

By default, SQL Server compresses data streams where appropriate for availability groups. Compression reduces network traffic, increases CPU load, and may induce latency. You must be a member of the sysadmin fixed server role to enable compression. The following table shows when SQL Server uses compression for availability group log streams:

Scenario Compression Setting
Synchronous-commit replica Not compressed
Asynchronous- commit replicas Compressed
During automatic seeding Not compressed

Trace flags for availability group compression

For most scenarios Microsoft does not recommend changing these settings. You can use global trace flags to test changing these settings. SQL Server applies global trace flags to the entire instance. All of the availability groups in the instance will be affected by these settings.

The following table shows trace flags that will change the default compression behavior for SQL Server.

Trace flag Description
1462 Disables log stream compression for Availability Groups with asynchronous replicas. This feature is enabled by default on asynchronous replicas to optimize network bandwidth.
9567 Enables compression of the data stream for Availability Groups during automatic seeding. During automatic seeding, compression can significantly reduce the transfer time and will increase the load on the processor.
9592 Enables log stream compression for Availability Groups with synchronous replicas. This feature is disabled by default on synchronous replicas because compression adds latency. Log stream compression is enabled by default for asynchronous replicas.

Resources

Database Engine Startup Options

Automatic Seeding

Always On Prerequisites