Monthly Archives: January 2011

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.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]  = 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.

Check Restore Backup Shrink execution time & estimated time

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:

Here you go:

Start your Restore/Backup or Shrink operation from SQL Query or directly from SSMS Interface.

You must find out what is your session_id.

1. Identify your process :

  • Status (Runnable)
  • Login (yours)
  • DBname
Use master
sp_who2 active

2. Find out the percent done and estimated time of completion:

SELECT    percent_complete, start_time, command,,
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id where session_id=93

3. General Information about Shrink
Let me point 3 important things concerning the Shrink operation:

  1. 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.
  2. Nevertheless sometimes it is the single option when massive deletes (or index removal) are done and you need desperately the space.
  3. 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.
  4. 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.

    SELECT * INTO master.dbo.TEMPINDEXES FROM sys.dm_db_index_physical_stats (5, NULL, NULL, NULL , 'LIMITED')
    SELECT t.database_id,, s.type,t.object_id, t.index_type_desc, t.avg_fragmentation_in_percent, t.page_count, *
    FROM master.dbo.TEMPINDEXES t inner join sysobjects s on t.[OBJECT_ID]  =
    order by t.avg_fragmentation_in_percent desc