Indexing Maintenance Solution for Replication

Spread the love

Like any other DBA, I fell into the trap of using straight maintenance solution using Reorganize operation for Indexes with avg fragmentation with 30% or less with Index Rebuild for avg fragmentation greater than 30%.

Well above approach works fine in common scenarios, but can create problems for servers using transaction log based High Availability technologies, such as AlwaysOn Availability Groups, database mirroring, log shipping, and replication. Both index rebuild and reorganize introduce heavy transaction log activity and generate a large number of log records. This becomes an issue in case of node failover, server with limited storage, database file with restricted growth, wrong file auto growth setting, or database with high VLF counts.

The best option for servers with High Availability is to identify kind of server workload (OLTP/OLAP/mixed), fill factor (based on Page Splits/sec), fragmentation, underlying storage load (random/sequential), Index Scans vs Index Searches, job time frame (low activity outside business hours) etc. After calculating all the above factors, all we need is to have a robust Index Maintenance solution. This is where I find Ola Hallengren’s SQL Server Maintenance Solution a perfect fit.

Below is the final Solution we are using in our environment using Ola code:-

1) Index Job [DBA – IndexOptimize – User_Databases_Minus_Repl] – User Databases Except Dbs Involved in Replication

Step 01=> Process Medium Size Indexes (upto 10 GB in size) with REBUILD operation
avg fragmentation > 30%, Minimum Pages = 1000, Max Pages = 1310720, DOP = 8

Step 02=> Process Large Size Indexes (greater than 10 GB in size upto 50 GB) with REORGANIZE operation
avg fragmentation > 30%, Minimum Pages = 1310720, Max Pages = 6553600

2) Index Job [DBA – IndexOptimize – User_Repl_Databases] – User Databases Involved in Replication

Step 01=> Process Medium Size Indexes (upto 10 GB in size) with REBUILD operation
avg fragmentation > 30%, Minimum Pages = 1000, Max Pages = 1310720, DOP = 8, Delay b/w Index = 10 Minutes

Step 02=> Process Large Size Indexes (greater than 10 GB in size upto 50 GB) with REORGANIZE operation
avg fragmentation > 30%, Minimum Pages = 1310720, Max Pages = 6553600

This way we are taking indexes with more than 1000 pages having 30% or more fragmentation, and using REORGANIZE for large Indexes (greater than 10 Gb). With Ola, we even have the choice to skip indexes of larger than a particular size threshold. But as advised by SQL experts, always customize the Ola code for your environment with parameters like Fill_Factor, SORT_IN_TEMPDB, UpdateStats etc before using any typical solution.

One can use below TSQL Code to find out details like total pages, record counts and size for tables/Indexes in all databases.

Friends, please feel free to correct me by comments. If you like the article, do Like & Share. Happy Coding 🙂

Add a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.