Sweet Baby

The following matters only for those who still run on SQL Server 2000, as since SQL Server 2005 the DBCC DBREINDEX and DBCC INDEXDEFRAG actually have been replaced by the ALTER INDEX REBUILD/REORGANIZE command (I don’t know if the following problem also affects ALTER INDEX).

Why executing anyway?

Well, Indexes will fragment over time, as data is inserted, modified or deleted anytime when processing NAV (or other) transactions. Basically it’s the same like a hard-disk-drive could fragment. The more fragmented an index is, the longer it takes to retrieve the required data from it; the SQL Server has to perform more “Page Reads". Having too many “Reads" slows down the performance as it is more time consuming and could cause too much I/O. Hence, to avoid performance issue it is necessary to periodically maintain the indexes. Thus, “maintaining indexes" actually means defragmenting indexes.

The DBCC methods:

To maintain indexes several options would be available:

DROP and re-CREATE the index (not recommended, not discussed here)

Run DBCC DBREINDEX (takes care about “external fragmentation", most efficient but heavily locking, restores index fill-factors)

Run DBCC INDEXDEFRAG (takes care about “internal fragmentation", less efficient but less blocking, compresses LOBs)

The DBCC re-indexing tasks are supported by SQL Server “Maintenance Plans", so it is easily possible to implement some kind of index maintenance. IMHO the minimum requirement is to defrag indexes at least once per week, preferably using the DBCC DBREINDEX (again, I’m talking about SQL Server 2000 here!).

But with large databases this could be a problem: the defragmentation could take very long, and when defragmenting all indexes of a database, the “Transaction Log" could grow to the size of the database itself. So, systems with small time-frames for maintenance will encounter problems, or those who perform “Transaction Log Shipping" (if the TLog backup (TRN) is supersize).

Index smaller than 100 “Pages" = do nothing

“Logical Fragmentation" < 10% = do nothing

“Logical Fragmentation" >= 10% = DBCC DBREINDEX