Thursday, June 23, 2011

DBCC REINDEX vs DBCC INDEXDEFRAG

Note that DBCC DBREINDEX uses one big transaction, killing the operation in the middle would rollback the entire transaction.  It would not help you reduce log space consumption if you restart the index rebuild after killing.

I noticed that you were doing index rebuild monthly.  DBCC DBREINDEX is an offline operation.  While doing clustered index rebuild, the entire table is offline.  You should only consider rebuilding index if fragmentation affects your query performance. 

If after a while, your query performance goes down because of fragmentation, do you consider using DBCC INDEXDEFRAG to reduce fragmentation and improve space usage? 

Three biggest advantages of DBCC INDEXDEFRAG are

(1). If fragmentation is low (under 20%), you will use less log space than DBCC DBREINDEX;

(2). DBCC INDEXDEFRAG is an online operation and uses in-place page reorganization algorithm, hence space requirement for data is almost none and other query could access the data while defrag is going on;

(3). You can kill and restart in the middle of defrag and all finished work is preserved. 

The major drawbacks if DBCC INDEXDEFRAG are

(1). It is slower than DBCC DBREINDEX if fragmentation is high;

(2). Index statistics are not updated after DBCC INDEXDEFRAG

0 comments:

Post a Comment