Recommendations for Index Maintenance with AlwaysOn Availability Groups

Published Jan 15 2019 04:27 PM 15.6K Views
Microsoft

First published on MSDN on Mar 03, 2015

 

 

SYMPTOMS

 

Consider the following scenario



    • The database is part of AlwaysOn Availability Groups
    • You run long and log-intensive transactions like Index maintenance/rebuilds

 

You observe one or more of the following symptoms:



    • Poor performing DML operations in availability databases on the primary replica if synchronous secondary replicas are present.
    • Huge log send queue in the primary.
    • Considerable log growth in those availability databases in which the index rebuild occurs
    • Redo backlog in secondary replicas.

 

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, 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.

 

 

 

MITIGATION

 

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:



    • Run index maintenance during off peak period if there is any.
    • Frequency of the maintenance should be optimized with the goal of minimizing impact of log generation. You can do maintenance in phases, like each phase doing a subset of indexes at a particular time, instead of considering all indexes at a single go.
    • Consider primarily updating statistics: statistics update with full scan is the primary benefit from index maintenance. Index fragmentation is rarely, if ever, the root cause for slow performance issues. Once index pages are read into memory from disk, on-disk fragmentation has little significance. 
    • Rebuild indexes based on true need / impact to your production environment. This script https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html shows how to achieve dynamic index maintenance -

        • Ignore heaps and small tables

        • Check the fragmentation level of each index

        • If the fragmentation level is low (say less than 10%) - do nothing

        • If the fragmentation level is medium (say in between 10 to 30%) - reorganize the index

        • If the fragmentation level is high (say more than 30%) - rebuild the index


 

    • Use MAXDOP setting in the ALTER INDEX command to reduce the concurrent index alteration activity. Operations such as creating, rebuilding, or dropping indexes can be resource intensive and can cause insufficient resources for other applications and database operations for the duration of the index operation. When this problem occurs, you can manually configure the maximum number of processors that are used to run the index statement by limiting the number of processors to use for the index operation. Lower MADOP setting can also reduce fragmentation with online Index rebuild operation. Below is an example of ALTER INDEX  REBUILD with MAXDOP =1:




USE [AdventureWorks2014]
GO
ALTER INDEX [PK_Employee_BusinessEntityID]
ON [HumanResources].[Employee]
REBUILD WITH (MAXDOP=1, ONLINE= ON);
GO

 



    • Consider using table partitioning. This way you can rebuild portions of the index piece by piece by using ALTER INDEX…REBUILD PARTITION. SQL Server 2014 supports the ONLINE rebuild of partitioned indexes. Below is a sample script to rebuild only on partition 1:

 


USE [AdventureWorks2014]
GO
ALTER INDEX [IX_TransactionHistory_ProductID]
ON [Production].[TransactionHistory]
REBUILD PARTITION = 1
WITH (ONLINE = ON);
GO



    • For SQL server 2014 additional ALTER INDEX parameters like WAIT_AT_LOW_PRIORITY, MAX_DURATION, and ABORT_AFTER_WAIT can also be used. Following example rebuilds index with the ONLINE option including the low priority lock options:




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:



    • A busy secondary such as, secondary with resource bottleneck or a large reporting workload on the secondary replica, can slow down the performance of the secondary replica because of resource contention, and the redo thread can fall behind. The redo thread on the secondary replica can also be blocked from making data definition language (DDL) changes by a long-running read-only query. The diagnosis and mitigations steps for these issues are discussed in the article Troubleshoot: Availability Group Exceeded RTO .

 

 

    • We recommend that you always switch to the High Performance power plan for all replica machines for all operating systems. In some circumstances this can have better performance for the single threaded redo process.



 

 

REFERENCES

 

1 Comment
Regular Visitor

The supposed "Best Practices" are not best practices and we're never meant to be taken as "Best Practices".  The recommendation for the original author and creator of REORGANIZE and REBUILD is to, and I quote... "take those numbers with a pinch of salt and don’t treat them as absolute."

https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-f...

 

I'll also tell you that REORGANIZE does NOT work the way most people think it does.  It does NOT "follow the Fill Factor" in any way ever close to a REBUILD.  Instead, all it does is it tries to shrink the index by combining "some" pages UP TO the Fill Factor.  It does NOT create any Free Space above the Fill Factor.  The bad part about that is that 99.99% of the people in the world use 5 or 10% to 30% LOGICAL fragmentation to identify when REORGANIZE should be done and they do a REBUILD only if the LOGICAL fragmentation goes over 30%.  Since REORGANIZE DOES remove LOGICAL fragmentation, it very quickly gets "stuck" on indexes and they frequently never be rebuilt ever again.  That means that no Free Space is created above the Fill Factor.  That means that virtually every index you lowered the Fill Factor to prevent fragmentation is NOT working correctly and the fragmentation such an action was supposed to prevent is now perpetual and actually gets worse after each REORGANIZE because all the critical space above the Fill Factor is permanently full because REORGANIZE removes free space below the Fill Factor and compresses pages.

 

The phenomena prevents perfect wide or random distribution indexes from working correctly.  For example, a correctly maintained Random GUID index (and I have the proof in multiple repeatable tests) can withstand 100,000 per day inserts for 58 days (that's a total of 5.8 MILLION rows, folks!) with less than 1% logical fragmentation and absolutely no index maintenance during that entire period.  The way I did that was to STOP using REORGANIZE.  In a parallel test, REORGANIZE cause fairly massive and perpetual page splits all day every day during that same 58 day period along with fragmentation going over 5% and needing another REORGANIZE every 4 days.

 

During that same testing, it was proven that it's actually better to do NO index maintenance than it is to ever use REORGANIZE.  The only 2 places that you should use reorganize is to compress LOBs (it even sucks at that and needs a rebuild right afterwards) and to remove physical fragmentation on an Insert Hot Spot cause by an Insert/Update pattern on ever increasing indexes.  And, other fragmenting indexes are also affected in a similar manner... not just Random GUIDs.

 

For anyone interested (and, no.... this is NOT meant to be any form of spam), I'm giving the hour long version of my 2.5 hour presentation on the subject on the 28th of July at "EightKB".  You can register to attend at the following URL...

https://eightkb.online/

 

--Jeff Moden

 

%3CLINGO-SUB%20id%3D%22lingo-sub-318518%22%20slang%3D%22en-US%22%3ERecommendations%20for%20Index%20Maintenance%20with%20AlwaysOn%20Availability%20Groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-318518%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Mar%2003%2C%202015%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%20SYMPTOMS%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConsider%20the%20following%20scenario%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EThe%20database%20is%20part%20of%20AlwaysOn%20Availability%20Groups%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EYou%20run%20long%20and%20log-intensive%20transactions%20like%20Index%20maintenance%2Frebuilds%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20observe%20one%20or%20more%20of%20the%20following%20symptoms%3A%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EPoor%20performing%20DML%20operations%20in%20availability%20databases%20on%20the%20primary%20replica%20if%20synchronous%20secondary%20replicas%20are%20present.%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EHuge%20log%20send%20queue%20in%20the%20primary.%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EConsiderable%20log%20growth%20in%20those%20availability%20databases%20in%20which%20the%20index%20rebuild%20occurs%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3ERedo%20backlog%20in%20secondary%20replicas.%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%20CAUSE%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELarge%20maintenance%20operations%20like%20ALTER%20INDEX%20or%20CREATE%20INDEX%20can%20generate%20huge%20amounts%20of%20logged%20changes%20by%20the%20nature%20of%20the%20operation.%20These%20transactions%20can%20utilize%20parallelism%20to%20use%20multiple%20threads%20generating%20logged%20changes%20to%20accomplish%20the%20large%20transaction.%20This%20is%20in%20addition%20to%20the%20log%20generated%20by%20the%20regular%20day%20to%20day%20operations%20of%20your%20application.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20synchronous%20commit%20environments%2C%20these%20large%20transactions%20can%20create%20contention%20with%20other%20production%20changes%2C%20reducing%20the%20overall%20performance%20of%20the%20primary%20replica.%20Synchronous-commit%20mode%20emphasizes%20high%20availability%20over%20performance%2C%20at%20the%20cost%20of%20increased%20transaction%20latency.%20Under%20synchronous%20commit%20mode%2C%20transactions%20wait%20to%20send%20the%20transaction%20confirmation%20to%20the%20client%20until%20the%20secondary%20replica%20has%20hardened%20the%20log%20to%20disk.%26nbsp%3B%20All%20this%20logged%20activity%20is%20being%20captured%20by%20a%20per-database%20single%20threaded%20log%20capture%20thread.%20In%20addition%20encryption%20and%20compression%20routines%20are%20also%20single%20threaded.%20When%20these%20single-threaded%20routines%20are%20processing%20very%20large%20amounts%20of%20logged%20activity%2C%20your%20application%20transactions%20may%20suffer%20performance%20degradation.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELog-intensive%20transactions%20like%20rebuilding%20indexes%20can%20cause%20%3CA%20href%3D%22http%3A%2F%2Fsupport.microsoft.com%2Fkb%2F317375%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20the%20log%20file%20to%20grow%20significantly%20%3C%2FA%3E%20as%20the%20log%20cannot%20be%20truncated%20until%20redo%20has%20completed%20the%20changes%20in%20all%20secondary%20replicas.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%20MITIGATION%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETransactions%20like%20ALTER%20INDEX%20or%20CREATE%20INDEX%20are%20log%20intensive%20by%20nature.%20We%20cannot%20eliminate%20the%20log%20generation%20but%20we%20can%20do%20intelligent%20maintenance%20to%20reduce%20the%20impact%20of%20the%20index%20rebuild%20on%20production%20activities.%26nbsp%3B%20Here%20are%20some%20steps%20to%20minimize%20the%20impact%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%20Mitigation%20Steps%20From%20Index%20Maintenance%20Perspective%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20following%20strategies%20may%20reduce%20the%20contention%20index%20rebuilding%20has%20on%20your%20production%20environment%3A%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3ERun%20index%20maintenance%20during%20off%20peak%20period%20if%20there%20is%20any.%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EFrequency%20of%20the%20maintenance%20should%20be%20optimized%20with%20the%20goal%20of%20minimizing%20impact%20of%20log%20generation.%20You%20can%20do%20maintenance%20in%20phases%2C%20like%20each%20phase%20doing%20a%20subset%20of%20indexes%20at%20a%20particular%20time%2C%20instead%20of%20considering%20all%20indexes%20at%20a%20single%20go.%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3CLI%3EConsider%20primarily%20%3CSTRONG%3Eupdating%20statistics%3C%2FSTRONG%3E%3A%20statistics%20update%20with%20full%20scan%20is%20the%20primary%20benefit%20from%20index%20maintenance.%20Index%20fragmentation%20is%20rarely%2C%20if%20ever%2C%20the%20root%20cause%20for%20slow%20performance%20issues.%20Once%20index%20pages%20are%20read%20into%20memory%20from%20disk%2C%20on-disk%20fragmentation%20has%20little%20significance.%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3ERebuild%20indexes%20based%20on%20true%20need%20%2F%20impact%20to%20your%20production%20environment.%20This%20script%20%3CA%20href%3D%22https%3A%2F%2Fola.hallengren.com%2Fsql-server-index-and-statistics-maintenance.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3Ehttps%3A%2F%2Fola.hallengren.com%2Fsql-server-index-and-statistics-maintenance.html%3C%2FFONT%3E%3C%2FA%3E%20shows%20how%20to%20achieve%20dynamic%20index%20maintenance%20-%3C%2FDIV%3E%3CBR%20%2F%3E%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EIgnore%20heaps%20and%20small%20tables%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%3CBR%20%2F%3E%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3ECheck%20the%20fragmentation%20level%20of%20each%20index%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%3CBR%20%2F%3E%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EIf%20the%20fragmentation%20level%20is%20low%20(say%20less%20than%2010%25)%20-%20do%20nothing%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%3CBR%20%2F%3E%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EIf%20the%20fragmentation%20level%20is%20medium%20(say%20in%20between%2010%20to%2030%25)%20-%20reorganize%20the%20index%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%3CBR%20%2F%3E%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EIf%20the%20fragmentation%20level%20is%20high%20(say%20more%20than%2030%25)%20-%20rebuild%20the%20index%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EUse%20MAXDOP%20setting%20in%20the%20ALTER%20INDEX%20command%20to%20reduce%20the%20concurrent%20index%20alteration%20activity.%20Operations%20such%20as%20creating%2C%20rebuilding%2C%20or%20dropping%20indexes%20can%20be%20resource%20intensive%20and%20can%20cause%20insufficient%20resources%20for%20other%20applications%20and%20database%20operations%20for%20the%20duration%20of%20the%20index%20operation.%20When%20this%20problem%20occurs%2C%20you%20can%20%3CA%20href%3D%22https%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms189329.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20manually%20configure%20the%20maximum%20number%20of%20processors%20that%20are%20used%20to%20run%20the%20index%20statement%20%3C%2FA%3E%20by%20limiting%20the%20number%20of%20processors%20to%20use%20for%20the%20index%20operation.%20Lower%20MADOP%20setting%20can%20also%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fpsssql%2Farchive%2F2012%2F09%2F05%2Fhow-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20reduce%20fragmentation%20%3C%2FA%3E%20with%20online%20Index%20rebuild%20operation.%20Below%20is%20an%20example%20of%20ALTER%20INDEX%26nbsp%3B%20REBUILD%20with%20MAXDOP%20%3D1%3A%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%3CBR%20%2F%3EUSE%20%5BAdventureWorks2014%5D%20%3CBR%20%2F%3EGO%20%3CBR%20%2F%3EALTER%20INDEX%20%5BPK_Employee_BusinessEntityID%5D%20%3CBR%20%2F%3EON%20%5BHumanResources%5D.%5BEmployee%5D%20%3CBR%20%2F%3EREBUILD%20WITH%20(MAXDOP%3D1%2C%20ONLINE%3D%20ON)%3B%20%3CBR%20%2F%3EGO%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EConsider%20using%20table%20partitioning.%20This%20way%20you%20can%20rebuild%20portions%20of%20the%20index%20piece%20by%20piece%20by%20using%20ALTER%20INDEX%E2%80%A6REBUILD%20PARTITION.%20SQL%20Server%202014%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms188388.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20supports%20%3C%2FA%3E%20the%20ONLINE%20rebuild%20of%20partitioned%20indexes.%20Below%20is%20a%20sample%20script%20to%20rebuild%20only%20on%20partition%201%3A%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%3CBR%20%2F%3EUSE%20%5BAdventureWorks2014%5D%20%3CBR%20%2F%3EGO%20%3CBR%20%2F%3EALTER%20INDEX%20%5BIX_TransactionHistory_ProductID%5D%20%3CBR%20%2F%3EON%20%5BProduction%5D.%5BTransactionHistory%5D%20%3CBR%20%2F%3EREBUILD%20PARTITION%20%3D%201%20%3CBR%20%2F%3EWITH%20(ONLINE%20%3D%20ON)%3B%20%3CBR%20%2F%3EGO%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EFor%20SQL%20server%202014%20additional%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms188388.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20ALTER%20INDEX%20%3C%2FA%3E%20parameters%20like%20WAIT_AT_LOW_PRIORITY%2C%20MAX_DURATION%2C%20and%20ABORT_AFTER_WAIT%20can%20also%20be%20used.%20Following%20example%20rebuilds%20index%20with%20the%20ONLINE%20option%20including%20the%20low%20priority%20lock%20options%3A%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%3CBR%20%2F%3EUSE%20%5BAdventureWorks2014%5D%20%3CBR%20%2F%3EGO%20%3CBR%20%2F%3EALTER%20INDEX%20ALL%20ON%20Production.Product%20%3CBR%20%2F%3EREBUILD%20WITH%20%3CBR%20%2F%3E(ONLINE%20%3D%20ON%20(%20WAIT_AT_LOW_PRIORITY%20(%20MAX_DURATION%20%3D%204%20MINUTES%2C%20ABORT_AFTER_WAIT%20%3D%20BLOCKERS%20)))%3B%20%3CBR%20%2F%3EGO%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%20Mitigation%20Steps%20From%20Availability%20Group%20Perspective%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20%3CSTRONG%3E%20synchronous%20commit%20environments%20%3C%2FSTRONG%3E%20%2C%20before%20issuing%20long%20and%20log-intensive%20transactions%20like%20ALTER%20INDEX%20or%20CREATE%20INDEX%2C%20you%20may%20additionally%20consider%20to%20make%20all%20synchronous%20replicas%20asynchronous%20to%20help%20reduce%20the%20transactional%20latency.%20Once%20the%20index%20rebuild%20transactions%20are%20completed%2C%20the%20commit%20mode%20should%20be%20switched%20back%20to%20synchronous.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20both%20%3CSTRONG%3E%20SYNCHRONOUS%20AND%20Asynchronous%20%3C%2FSTRONG%3E%20environments%2C%20in%20general%20any%20step%20that%20can%20help%20with%20redo%20performance%20will%20positively%20impact%20long%20and%20log-intensive%20transactions%20on%20AG%20environments.%20Here%20are%20some%20key%20points%20to%20keep%20in%20mind%3A%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EA%20busy%20secondary%20such%20as%2C%20secondary%20with%20resource%20bottleneck%20or%20a%20large%20reporting%20workload%20on%20the%20secondary%20replica%2C%20can%20slow%20down%20the%20performance%20of%20the%20secondary%20replica%20because%20of%20resource%20contention%2C%20and%20the%20redo%20thread%20can%20fall%20behind.%20The%20redo%20thread%20on%20the%20secondary%20replica%20can%20also%20be%20blocked%20from%20making%20data%20definition%20language%20(DDL)%20changes%20by%20a%20long-running%20read-only%20query.%20The%20diagnosis%20and%20mitigations%20steps%20for%20these%20issues%20are%20discussed%20in%20the%20article%20%3CA%20href%3D%22https%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fdn135336(v%3Dsql.110).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Troubleshoot%3A%20Availability%20Group%20Exceeded%20RTO%20%3C%2FA%3E%20.%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EYou%20need%20to%20periodically%20check%20AG%20databases%20to%20make%20sure%20they%20do%20not%20have%20too%20many%20Virtual%20Log%20Files%20(VLFs)%20which%20can%20severely%20impact%20redo%20process%20of%20the%20secondary.%20Diagnosis%20and%20corrective%20steps%20are%20discussed%20in%20the%20article%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fsaponsqlserver%2Farchive%2F2012%2F02%2F22%2Ftoo-many-virtual-log-files-vlfs-can-cause-slow-database-recovery.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Too%20Many%20Virtual%20Log%20Files%20(VLFs)%20Can%20Cause%20Slow%20Database%20Recovery%20%3C%2FA%3E%20.%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CDIV%3EWe%20recommend%20that%20you%20always%20switch%20to%20the%20%3CA%20href%3D%22http%3A%2F%2Fsupport.microsoft.com%2Fkb%2F2207548%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20High%20Performance%20%3C%2FA%3E%20power%20plan%20for%20all%20replica%20machines%20for%20all%20operating%20systems.%20In%20some%20circumstances%20this%20can%20have%20better%20performance%20for%20the%20single%20threaded%20redo%20process.%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%20REFERENCES%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Fsupport.microsoft.com%2Fkb%2F317375%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EA%20transaction%20log%20grows%20unexpectedly%20or%20becomes%20full%20in%20SQL%20Server%20%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CDIV%3E%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fjoaol%2Farchive%2F2008%2F01%2F28%2Fscript-to-rebuild-and-reorganize-database-indexes-sql-server-2005.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EScript%20to%20appropriate%20rebuild%2Freorganize%20database%20indexes%20-%20SQL%20Server%202005%20%3C%2FA%3E%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CDIV%3E%3CA%20href%3D%22https%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms189329.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EConfigure%20Parallel%20Index%20Operations%20%3C%2FA%3E%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CDIV%3E%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fpsssql%2Farchive%2F2012%2F09%2F05%2Fhow-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EHow%20It%20Works%3A%20Online%20Index%20Rebuild%20-%20Can%20Cause%20Increased%20Fragmentation%20%3C%2FA%3E%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CDIV%3E%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms188388.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EALTER%20INDEX%20(Transact-SQL)%20%3C%2FA%3E%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CDIV%3E%3CA%20href%3D%22https%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fdn135336(v%3Dsql.110).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ETroubleshoot%3A%20Availability%20Group%20Exceeded%20RTO%20%3C%2FA%3E%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CDIV%3E%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fsaponsqlserver%2Farchive%2F2012%2F02%2F22%2Ftoo-many-virtual-log-files-vlfs-can-cause-slow-database-recovery.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EToo%20Many%20Virtual%20Log%20Files%20(VLFs)%20Can%20Cause%20Slow%20Database%20Recovery%20%3C%2FA%3E%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CDIV%3E%3CA%20href%3D%22http%3A%2F%2Fsupport.microsoft.com%2Fkb%2F2964518%3Fwa%3Dwsignin1.0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ERecommended%20updates%20and%20configuration%20options%20for%20SQL%20Server%202012%20and%20SQL%20Server%202014%20used%20with%20high-performance%20workloads%20%3C%2FA%3E%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-318518%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20published%20on%20MSDN%20on%20Mar%2003%2C%202015%20%26nbsp%3BSYMPTOMSConsider%20the%20following%20scenarioThe%20database%20is%20part%20of%20AlwaysOn%20Availability%20GroupsYou%20run%20long%20and%20log-intensive%20transactions%20like%20Index%20maintenance%2FrebuildsYou%20observe%20one%20or%20more%20of%20the%20following%20symptoms%3APoor%20performing%20DML%20operations%20in%20availability%20databases%20on%20the%20primary%20replica%20if%20synchronous%20secondary%20replicas%20are%20present.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-318518%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EHigh%20Availability%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EStorage%20Engine%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2478963%22%20slang%3D%22en-US%22%3ERe%3A%20Recommendations%20for%20Index%20Maintenance%20with%20AlwaysOn%20Availability%20Groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2478963%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20supposed%20%22Best%20Practices%22%20are%20not%20best%20practices%20and%20we're%20never%20meant%20to%20be%20taken%20as%20%22Best%20Practices%22.%26nbsp%3B%20The%20recommendation%20for%20the%20original%20author%20and%20creator%20of%20REORGANIZE%20and%20REBUILD%20is%20to%2C%20and%20I%20quote...%20%22take%20those%20numbers%20with%20a%20pinch%20of%20salt%20and%20don%E2%80%99t%20treat%20them%20as%20absolute.%22%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.sqlskills.com%2Fblogs%2Fpaul%2Fwhere-do-the-books-online-index-fragmentation-thresholds-come-from%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.sqlskills.com%2Fblogs%2Fpaul%2Fwhere-do-the-books-online-index-fragmentation-thresholds-come-from%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20also%20tell%20you%20that%20REORGANIZE%20does%20NOT%20work%20the%20way%20most%20people%20think%20it%20does.%26nbsp%3B%20It%20does%20NOT%20%22follow%20the%20Fill%20Factor%22%20in%20any%20way%20ever%20close%20to%20a%20REBUILD.%26nbsp%3B%20Instead%2C%20all%20it%20does%20is%20it%20tries%20to%20shrink%20the%20index%20by%20combining%20%22some%22%20pages%20UP%20TO%20the%20Fill%20Factor.%26nbsp%3B%20It%20does%20NOT%20create%20any%20Free%20Space%20above%20the%20Fill%20Factor.%26nbsp%3B%20The%20bad%20part%20about%20that%20is%20that%2099.99%25%20of%20the%20people%20in%20the%20world%20use%205%20or%2010%25%20to%2030%25%20LOGICAL%20fragmentation%20to%20identify%20when%20REORGANIZE%20should%20be%20done%20and%20they%20do%20a%20REBUILD%20only%20if%20the%20LOGICAL%20fragmentation%20goes%20over%2030%25.%26nbsp%3B%20Since%20REORGANIZE%20DOES%20remove%20LOGICAL%20fragmentation%2C%20it%20very%20quickly%20gets%20%22stuck%22%20on%20indexes%20and%20they%20frequently%20never%20be%20rebuilt%20ever%20again.%26nbsp%3B%20That%20means%20that%20no%20Free%20Space%20is%20created%20above%20the%20Fill%20Factor.%26nbsp%3B%20That%20means%20that%20virtually%20every%20index%20you%20lowered%20the%20Fill%20Factor%20to%20prevent%20fragmentation%20is%20NOT%20working%20correctly%20and%20the%20fragmentation%20such%20an%20action%20was%20supposed%20to%20prevent%20is%20now%20perpetual%20and%20actually%20gets%20worse%20after%20each%20REORGANIZE%20because%20all%20the%20critical%20space%20above%20the%20Fill%20Factor%20is%20permanently%20full%20because%20REORGANIZE%20removes%20free%20space%20below%20the%20Fill%20Factor%20and%20compresses%20pages.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20phenomena%20prevents%20perfect%20wide%20or%20random%20distribution%20indexes%20from%20working%20correctly.%26nbsp%3B%20For%20example%2C%20a%20correctly%20maintained%20Random%20GUID%20index%20(and%20I%20have%20the%20proof%20in%20multiple%20repeatable%20tests)%20can%20withstand%20100%2C000%20per%20day%20inserts%20for%2058%20days%20(that's%20a%20total%20of%205.8%20MILLION%20rows%2C%20folks!)%20with%20less%20than%201%25%20logical%20fragmentation%20and%20absolutely%20no%20index%20maintenance%20during%20that%20entire%20period.%26nbsp%3B%20The%20way%20I%20did%20that%20was%20to%20STOP%20using%20REORGANIZE.%26nbsp%3B%20In%20a%20parallel%20test%2C%20REORGANIZE%20cause%20fairly%20massive%20and%20perpetual%20page%20splits%20all%20day%20every%20day%20during%20that%20same%2058%20day%20period%20along%20with%20fragmentation%20going%20over%205%25%20and%20needing%20another%20REORGANIZE%20every%204%20days.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDuring%20that%20same%20testing%2C%20it%20was%20proven%20that%20it's%20actually%20better%20to%20do%20NO%20index%20maintenance%20than%20it%20is%20to%20ever%20use%20REORGANIZE.%26nbsp%3B%20The%20only%202%20places%20that%20you%20should%20use%20reorganize%20is%20to%20compress%20LOBs%20(it%20even%20sucks%20at%20that%20and%20needs%20a%20rebuild%20right%20afterwards)%20and%20to%20remove%20physical%20fragmentation%20on%20an%20Insert%20Hot%20Spot%20cause%20by%20an%20Insert%2FUpdate%20pattern%20on%20ever%20increasing%20indexes.%26nbsp%3B%20And%2C%20other%20fragmenting%20indexes%20are%20also%20affected%20in%20a%20similar%20manner...%20not%20just%20Random%20GUIDs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20anyone%20interested%20(and%2C%20no....%20this%20is%20NOT%20meant%20to%20be%20any%20form%20of%20spam)%2C%20I'm%20giving%20the%20hour%20long%20version%20of%20my%202.5%20hour%20presentation%20on%20the%20subject%20on%20the%2028th%20of%20July%20at%20%22EightKB%22.%26nbsp%3B%20You%20can%20register%20to%20attend%20at%20the%20following%20URL...%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Feightkb.online%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Feightkb.online%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E--Jeff%20Moden%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Apr 12 2019 08:21 AM
Updated by: