Shulou(Shulou.com)06/01 Report--

Performance considerations for creating availability groups with automatic seed settings

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 the secondary example, SQL Server uses one thread for each LUN to initialize the database.

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

Automatic seed setup is a single-threaded process that can handle up to 5 databases. For example, for an availability group with 2 databases, there are 3 secondary replicas. Then the number of VDI threads on the master copy is actually 5-12-60. A single thread affects performance, especially when there is more than one database in an availability group.

Automatic seed setting can use compression, but it is disabled by default. Set the tracking flag 9567 on the primary copy to enable data stream compression during automatic seed setting. This can significantly reduce the transmission time set by the automatic seed, but it also increases CPU usage.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.