Scheduling index rebuild jobs

Question

I have a MS-SQL 2014 SP1 - two node AlwaysOn AG (One Primary and an read-only secondary) with about 10 databases sizing 20-30GB in total. I have some queries regarding scheduling index rebuild and stats gathering.

I have planned to schedule it on weekly basis through maintenance plans. Usually on sunday nights where there will be no load at all on the primary.

Should I run the job in primary or in replica? If I run it only on secondary then what benefits do I get? or vice versa? (Meaning should I run on both the nodes)

Is corruption likely to happen if I use SP1 and index rebuild via maintenance plans? As mentioned here

score 10 · Accepted Answer · edited Apr 13 '17 at 12:42

You should use smart index maintenance scripts as opposed to maintenance task.

Should I run the job in primary or in replica?

Yes, as they get replicated on secondary replicas.

If I run it only on secondary then what benefits do I get?

you cannot run on secondary replica.

Index maintenance generate a lot of transaction log activity that affects your RTO and RPO. Also, it increases the redo backlog on secondary replicas.

You should follow Recommendations for Index Maintenance with AlwaysOn Availability Groups :

Find a quiet time or during planned maintenance time run your index maintenance.
If possible switch from Synchronous mode to Async mode. Once index maintenance is done, switch it back to what it was.
Take frequent log backups so that the log file does not grow out of control.
Since you are on sql server 2014, you should use WAIT_AT_LOW_PRIORITY, MAX_DURATION, and ABORT_AFTER_WAIT
If you are using online index rebuilds, then use MAXDOP = 1

Also, note that a Partitioned Table May Limit the Runtime MAX DOP of Create/Alter Index

Is corruption likely to happen if I use SP1 and index rebuild via maintenance plans?

Since you are on SP1 (12.0.2370 or greater) , you should be fine.

score 1 · Answer 2 · answered Oct 23 '17 at 11:20

I think there is another way to rebuild fragmented index, You can create an sp and scheduled it via SQL Server Agent or via task scheduler. The below query rebuild or Reorganize only the necessary Indexes instead of all index rebuild, As a general recommendation the index need to rebuild that's get fragment percentage more than 30% and reorganize the index that has fragment percentage in between 10% to 30%

Find more details Click Hare

The below script rebuilding index one by one, so it won't be an expansive operation on production database.

--- Rebuild/Reorganize Index
CREATE TABLE #fragment_tbl
(
 DatabaseName SYSNAME
 , SchemaName SYSNAME
 , TableName SYSNAME
 , IndexName SYSNAME
 , [Fragmentation%] FLOAT
)
INSERT INTO #fragment_tbl
SELECT
 DB_NAME(DB_ID()) AS DatabaseName
 , ss.name AS SchemaName
 , OBJECT_NAME (s.object_id) AS TableName
 , i.name AS IndexName
 , s.avg_fragmentation_in_percent AS [Fragmentation%]
FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'SAMPLED') s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID()
AND i.index_id != 0
AND s.record_count > 0
AND o.is_ms_shipped = 0
DECLARE @RebuildSQL NVARCHAR(MAX)
SET @RebuildSQL = ''
SELECT
 @RebuildSQL = @RebuildSQL +
CASE
 WHEN [Fragmentation%] > 30
   THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
      + QUOTENAME(SchemaName) + '.'
      + QUOTENAME(TableName) + ' REBUILD;'
 WHEN [Fragmentation%] > 10
    THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
    + QUOTENAME(SchemaName) + '.'
    + QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #fragment_tbl
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset

Scheduling index rebuild jobs

2 Answers2