First published on MSDN on Mar 03, 2015
SYMPTOMS
Consider the following scenario
You observe one or more of the following symptoms:
CAUSE
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, SQL Server waits 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.
MITIGATION
Transactions like ALTER INDEX or CREATE INDEX are log intensive by nature. You can't eliminate the log records generated but you 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
In the infrequent cases where you do need to reorganize or re-build index, consider these:
USE [AdventureWorks2014]
GO
ALTER INDEX [PK_Employee_BusinessEntityID]
ON [HumanResources].[Employee]
REBUILD WITH (MAXDOP=1, ONLINE= ON);
GO
USE [AdventureWorks2014]
GO
ALTER INDEX [IX_TransactionHistory_ProductID]
ON [Production].[TransactionHistory]
REBUILD PARTITION = 1
WITH (ONLINE = ON);
GO
USE [AdventureWorks2014]
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS )));
GO
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:
REFERENCES
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.