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.
|
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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
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 ['+@_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 🙂
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.