SQL Server AlwaysOn AG Automatic initialization (10)

Performance considerations for an availability group that creates an automatic seed setting

SQL Server uses a fixed number of threads for automatic seeding. In the primary instance, SQL Server uses one thread for each LUN to read the changes. In a secondary instance, SQL Server uses a thread for each LUN to initialize the database.

During automatic seeding, the log backup of the database on the primary replica continues to grow and cannot be truncated until the automatic seeding of the database is complete. The transaction log can then be truncated using a log backup. A long initialization process on a busy database can lead to significant transaction log growth. For deferred transaction truncation, you can refer to factors that affect the truncation of deferred transactions.

Automatic seeding is a single-threaded process that can handle up to 5 databases. For example, there are 3 secondary replicas for availability groups that contain 2 databases. The number of VDI threads on the primary replica is actually 5*12=60. Single threading affects performance, especially when there is more than one database for an availability group.

Automatic seeding can use compression, but it is disabled by default. Set trace flag 9567 on the primary replica to enable data flow compression during automatic seeding. This can significantly reduce the transfer time for automatic seeding, but it also increases CPU utilization.

SQL Server AlwaysOn AG Automatic initialization (10)