First published on MSDN on Mar 03, 2015
Consider the following scenario
You observe one or more of the following symptoms:
Large maintenance operations like ALTER INDEX or CREATE INDEX can generate huge amounts of logged changes by the nature of the operation. These transactions can utilize parallelism to use multiple threads generating logged changes to accomplish the large transaction. This is in addition to the log generated by the regular day to day operations of your application.
For synchronous commit environments, these large transactions can create contention with other production changes, reducing the overall performance of the primary replica. Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency. Under synchronous commit mode, transactions wait to send the transaction confirmation to the client until the secondary replica has hardened the log to disk. All this logged activity is being captured by a per-database single threaded log capture thread. In addition encryption and compression routines are also single threaded. When these single-threaded routines are processing very large amounts of logged activity, your application transactions may suffer performance degradation.
Log-intensive transactions like rebuilding indexes can cause the log file to grow significantly as the log cannot be truncated until redo has completed the changes in all secondary replicas.
Transactions like ALTER INDEX or CREATE INDEX are log intensive by nature. We cannot eliminate the log generation but we can do intelligent maintenance to reduce the impact of the index rebuild on production activities. Here are some steps to minimize the impact:
Mitigation Steps From Index Maintenance Perspective
The following strategies may reduce the contention index rebuilding has on your production environment:
ALTER INDEX [PK_Employee_BusinessEntityID]
REBUILD WITH (MAXDOP=1, ONLINE= ON);
ALTER INDEX [IX_TransactionHistory_ProductID]
REBUILD PARTITION = 1
WITH (ONLINE = ON);
ALTER INDEX ALL ON Production.Product
(ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS )));
Mitigation Steps From Availability Group Perspective
For synchronous commit environments , before issuing long and log-intensive transactions like ALTER INDEX or CREATE INDEX, you may additionally consider to make all synchronous replicas asynchronous to help reduce the transactional latency. Once the index rebuild transactions are completed, the commit mode should be switched back to synchronous.
For both SYNCHRONOUS AND Asynchronous environments, in general any step that can help with redo performance will positively impact long and log-intensive transactions on AG environments. Here are some key points to keep in mind:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.