SQL Server – Doing Index Maintenance when HADR solutions are in Place
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:-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SET NOCOUNT ON; DECLARE @LogSpaceUsedThreshold_GB DECIMAL(20,2); SET @LogSpaceUsedThreshold_GB = 50; IF OBJECT_ID('tempdb..#LogSpaceTable') IS NOT NULL TRUNCATE TABLE #LogSpaceTable; ELSE BEGIN CREATE TABLE #LogSpaceTable ( DbName varchar(200),LogSizeMB decimal(20,8), LogSpaceUsedPercent decimal(10,7), Status int ,LogSizeGB as cast(LogSizeMB / 1024 as decimal(20,2)) ,LogSpaceUsedGB as cast((LogSizeMB * (LogSpaceUsedPercent / 100)) / 1024 as decimal(20,2)) ) END -- Get Log Space Usage Metrics INSERT #LogSpaceTable EXEC('dbcc sqlperf(logspace)'); -- Stop the IndexOptimize Jobs if @LogSpaceUsedThreshold_GB is crossed IF EXISTS (SELECT * FROM #LogSpaceTable as s WHERE s.DbName = 'Cosmos' AND s.LogSpaceUsedGB >= @LogSpaceUsedThreshold_GB) AND DBA.dbo.fn_IsJobRunning('DBA - IndexOptimize_Modified - Cosmos') = 1 EXEC msdb..sp_stop_job @job_name = 'DBA - IndexOptimize_Modified - Cosmos'; -- Stop the IndexOptimize Jobs if @LogSpaceUsedThreshold_GB is crossed IF EXISTS (SELECT * FROM #LogSpaceTable as s WHERE s.DbName = 'SportsDB' AND s.LogSpaceUsedGB >= @LogSpaceUsedThreshold_GB) AND DBA.dbo.fn_IsJobRunning('DBA - IndexOptimize_Modified - SportsDB') = 1 EXEC msdb..sp_stop_job @job_name = 'DBA - IndexOptimize_Modified - SportsDB'; |
2. SQL Agent Job named [DBA – IndexOptimize_Modified – SportsDb]
This job executes below tsql code. The stored procedure DBA.dbo.IndexOptimize_Modified used in the following code can be found on my Github repository imajaydwivedi/SQLDBA-SSMS-Solution. This procedure is designed with below points:-
- IndexOptimize_Modified procedure uses Ola IndexOptimize procedure as core code to process each index one by one. It means, it supports all parameters supported by Ola IndexOptimize.
- 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).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
/* IndexOptimize [SportsDb] with @TimeLimit = 3.5 Hour */ EXECUTE DBA.dbo.IndexOptimize_Modified @Databases = 'SportsDb', -- Multiple databases can also be passed here @TimeLimit = 12600, -- 3.5 hours @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 50, @FragmentationLevel2 = 70, @MinNumberOfPages = 1000, @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 */ @LOBCompaction = 'Y', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @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 */ @LogToTable = 'Y' ,@forceReInitiate = 0 |
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 🙂
Related Posts
About The Author
Ajay Dwivedi
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.