Reindex on Mirrored Databases

The basic Index Rebuild or DBCC Reindex has a different impact on mirrored databases and can cause you more problems than you think.

Before starting the maintenance on Indexes check if:

  • Mirroring is set to high – safety or high – performance.
  • Space available on both servers (Principal/Mirror) for Log Files disk.
  • Space available on both servers (Principal/Mirror) for Data Files disk.
  • Space available on both servers (Principal/Mirror) for tempdb database.

1. Change Mirror Operating mode:
 
Now, if your databases are set in High – Safety operationg mode, change the setting in High – Performance. It will allow Principal server to perform the Reindex and not consider the answer from Mirror. Your goal is to minimize the downtime on Principal server. The Mirror server will catch up with Log restoration when the transaction will be sent to Mirror server not before committing on Principal.
 
2. Online vs. Offline Rebuild:
 
If you perform ALTER INDEX REBUILD with ONLINE option make sure you have the size of your indexes planned for Rebuild available on Data and Log Files disk, because the SQL will first create the NEW index allowing other queries to be run against the table using OLD index. Until the switch is done and OLD index dropped, 2 indexes will co-exist.

OFFLINE Rebuild will last 1,2 times less and use less disk space because the OLD index is dropped first and re-created from scratch. All activity on the table is exclusively locked.

Check the indexes size for a specific database:SELECT * INTO master.dbo.TEMPINDEXES FROM sys.dm_db_index_physical_stats (DB_ID(),null, null, NULL, ‘DETAILED’) SELECT t.database_id, s.name, s.type,t.object_id, t.index_type_desc, t.avg_fragmentation_in_percent, t.page_count, avg_record_size_in_bytes * record_count/1048576 as index_size, * FROM master.dbo.TEMPINDEXES t inner join sysobjects s on t.[OBJECT_ID]  = s.id order by t.avg_fragmentation_in_percent desc

 
3. Remove Snapshots:
 
During Index  Rebuild existing Database snapshots will get very big due to the complexity of the whole process. Also performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
For big Indexes (< 2 GB) I strongly recommend removing the snapshots, postpone the processes which use them (usually used in reporting by DW) and perform the Rebuild.

If you follow these steps, you will not have any problems at re-index procedure on mirrored databases.

  1. shailesh kumar

    HI
    This is awesome.

  2. thanks!

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>