Indexing Maintenance Solution for Replication

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.

SET NOCOUNT ON;

DECLARE @_dbName VARCHAR(2000);
SET @_dbName = NULL;

DECLARE @_sqlString NVARCHAR(MAX);
IF OBJECT_ID('tempdb..#TableSizeMetrics') IS NOT NULL
	DROP TABLE #TableSizeMetrics;
CREATE TABLE #TableSizeMetrics
(
	[DbName] [nvarchar](128) NULL,
	[object_id] [int] NOT NULL,
	[table_name] [nvarchar](257) NOT NULL,
	[type_desc] [nvarchar](120) NULL,
	[modify_date] [datetime] NOT NULL,
	[IndexName] [sysname] NULL,
	[index_type_desc] [nvarchar](60) NULL,
	[fill_factor] [tinyint] NOT NULL,
	[total_Table_rows] [bigint] NULL,
	[total_pages] [bigint] NULL,
	[size(MB)] [decimal](36, 2) NULL
) 

DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY FOR
	SELECT d.name FROM sys.databases as d where d.is_read_only = 0 and d.is_in_standby = 0 and d.database_id > 4 and d.state_desc = 'ONLINE'
		AND (@_dbName IS NULL OR d.name = @_dbName);

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @_dbName;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @_sqlString = '
USE ['[email protected]_dbName+'];
SELECT	[DbName] = DB_NAME()
		,t.[object_id]
		,[table_name] = s.[Name] + ''.'' + t.[name]
		,t.[type_desc], t.modify_date
		,[IndexName] = i.[name]
		,[index_type_desc] = i.[type_desc]
		,i.fill_factor 
		,p.[total_Table_rows]
		,[total_pages] = a.total_Index_pages
		,[size(MB)] = convert(decimal(36,2),(a.total_Index_pages * 8.0)/1024)
    FROM 
        sys.tables t
    INNER JOIN 
        sys.schemas s ON s.schema_id = t.schema_id
    INNER JOIN      
        sys.indexes i ON t.OBJECT_ID = i.object_id
    OUTER APPLY (
			SELECT SUM(P.rows) AS [total_Table_rows] 
			FROM sys.partitions p
			WHERE i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
		) p
	OUTER APPLY (
			SELECT [total_Index_pages] = SUM(a.total_pages)
			FROM sys.partitions p INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
			WHERE i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
		) a
    WHERE 
        t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
	ORDER BY DB_NAME(), s.Name, t.name, i.name;
';
	
	--PRINT @_sqlString;
	INSERT #TableSizeMetrics
	EXEC (@_sqlString);

	FETCH NEXT FROM dbCursor INTO @_dbName;
END

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #TableSizeMetrics;

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

Leave a Reply

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