When I joined my current organization 2 years back, the biggest problem I faced as DBA was whenever there was Replication, log shipping, Mirroring, or AlwaysOn, the Index Maintenance jobs were disabled. Point being, index maintenance generates a lot of transaction logs which introduces a delay in HADR. For solution like replication/log shipping, it becomes a mess to resolve. In short, my DBA teammates were happier to resolve performance slowness P1s by doing index rebuild of one or two tables rather than doing regular Index Maintenance that created a huge log backlog.
So, I created an Index Maintenance solution that would wok in environments with replication, log shipping, mirroring or Always on in place. Below are the objects/script required for same:-
SQL Agent Job named [DBA – IndexOptimize – Repl Dbs – Log Space Monitoring]
SQL Agent Job named [DBA – IndexOptimize_Modified – SportsDB]
1. SQL Agent Job named [DBA – IndexOptimize – Repl Dbs – Log Space Monitoring]
This job has tsql code to analyze the Log file size used space for my Replication database, and notify DBAs in case the size grows over @LogSpaceUsedThreshold_GB (50 gb). Plus it will stop the corresponding index maintenance job for the particular database (say SportsDB). Code is as below:-
SET NOCOUNT ON;
(DbName varchar(200),LogSizeMB decimal(20,8),LogSpaceUsedPercent decimal(10,7),Status int
Parameter @TimeLimit is time in seconds after which no new indexes would be processed.
In Initial execution of procedure IndexOptimize_Modified, table DBA.dbo.IndexProcessing_IndexOptimize is created/populated with indexes of @Databases.
With subsequent executions of procedure IndexOptimize_Modified, unprocessed indexes (IsProcessed = 0) for @Databases present in table DBA.dbo.IndexProcessing_IndexOptimize are passed to Ola IndexOptimize one by one and are marked IsProcessed = 1 once done.
In case, at the start of procedure IndexOptimize_Modified, there are no indexes to be processed for any database from @Databases, then the table is re-populated with Indexes of that particular database.
Code uses such an algorithm so that indexes are processed in descending order of Page count, and in an alternate sequence of one replication database & another non-replication database index.
Plus it makes sure that replication indexes are processed after an interval of time (@_DelayLength in tsql code).
/* IndexOptimize [SportsDb] with @TimeLimit = 3.5 Hour */
@Databases='SportsDb',--Multiple databases can also be passed here
@SortInTempdb='Y',/* Enable it when [Cosmo] production Server since [tempdb] & [Cosmo] database are on separate disks */
@MaxDOP=1,/* Default = 3 on Cosmo server */
--@FillFactor=70,/* Recommendations says to start with 100, and keep decreasing based on Page Splits/Sec value of server. On Cosmo server, Page Splits/sec are very high. Avg 171 page splits/sec for Avg 354 Batch Requests/sec */
@Indexes='ALL_INDEXES',/* Default is not specified. Db1.Schema1.Tbl1.Idx1, Db2.Schema2.Tbl2.Idx2 */
--@Delay=120,/* Introduce 300 seconds of Delay b/w Indexes of Replicated Databases */
In addition, I am using below tsql code to find out Indexes that need maintenance, and are populated into table DBA.dbo.IndexProcessing_IndexOptimize .
This solution has been working fine for about a year now in my organization and is a critical part of database maintenance for servers with HADR solution. So I hope this can be helpful to others as well.
Friends, please feel free to correct me by comments. If you like the article, do Like & Share. Happy Coding 🙂
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.