Category Archives: Database Maintenance

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.

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.

Have you ever wondered how to estimate the time for a restore, backup or even check how much time will take your shrink operation?These days I was thrilled to find out that starting with SQL 2005 there are some dynamic views that can be queried for this info:

The Database shrinked will get very fragmented. This is because all the space free inside the database is located everywhere in many data files. This, by Shrink will be be given back to the OS forcing the existing data files to be re-arranged and so, the indexes formed in a certain order will loose their order.

Nevertheless sometimes it is the single option when massive deletes (or index removal) are done and you need desperately the space.

To have a successful operation: All user table indexes must be checked for fragmentation and perform dbcc dbreindex, alter index rebuild or alter index reorganize – which suites you best.

I personally prefer to do Alter Index Rebuild for indexes with fragmentation bigger than 30 % and page count bigger than 10000.

Here are some commands to collect the fragmentation inside one database also using dynamic views.

About me…

I am from Bucharest, Romania and I am working with SQL Servers since 2006. I have been working closely with all SQL versions starting with SQL Server 2000. I am a senior DBA at OpenSky company and spend my free time with my husband and my son.
See more about me and why I started this blog.