SQL Services News

Resolving Always On Availability Group Synchronisation Problems in Write Intensive Environments

12 Aug 2016
Database High Availability systems such as Database Mirroring and Always on Availability Groups rely, directly or indirectly, on what is being written to the transaction log to keep the secondary databases in sync. This can create a problem when there are processes with high transaction log writes. In particular the synchronisation process may get behind resulting in potential data loss, poor performance or a bloated log file. The question then is; Are there processes that could be modified to reduce writes to the transaction log? Fortunately there are at least some that are worth considering.
  • For both Always On and Mirroring, consider reviewing any code that deletes all records from a table, or drops and recreates a table. For a large table the most efficient way to remove all records is to TRUNCATE the table. A table TRUNCATE is a minimally logged transaction, significantly reducing log writes, and likewise pressure on the synchronisation.
  • For Enterprise Edition consider rebuilding indexes in TempDB. An index rebuild is a logged process and if done in the database the rebuild changes are all synchronising to the secondary database. If the rebuild is done in TempDB, the only action logged in the user database is the switch of the new index for the old one (think switching table partitions).
  • For almost any system, review the number of indexes and when they are needed. On a high write, low read system maintaining indexes is an expensive process, so the fewer you have the better. Consider having minimal indexing (possibly no indexes) during the write phase of a process, only adding relevant read indexes once loading is complete. This removes the constant modification and fragmentation of the index as records are added, and the need for a later index rebuild, reducing the related log writes and pressure on synchronisation.

Written by Leo Miller Used to resolve Always On Availability Group synchronisation problems on an exceptionally write intensive environment with multiple indexes per table on a schema with thousands of tables.
Click here to return to the news items...

Microsoft Gold Data, Silver BI partner © Copyright SQL Services Limited 2019. For information on how SQL Services processes personal data please refer to our group privacy statement on our Group Website URL Privacy Statement Oracle Partner Logo