SQL Server: Index defragmentation
DBCC INDEXDEFRAG: Index defragmentation is the process that reduces the amount of index fragmentation.This process does not hold any table locks long term while defragmenting an index, hence does not block running queries or updates. This is unlike the index building process or the re-indexing process when a table lock is enforced. The underlying table cannot be modified, truncated, or dropped while an online index operation is in process.To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has been completed; however, the log can be backed up during the index operation. It is not suggested to use on very fragmented indexes. Here is an example of MSDN as to what happens when an index is defragmented:
Figure: Index defragmentation in action .
DBCC DBREINDEX: Faster than dropping and re-creating, but during rebuilding a clustered-index, an exclusive table lock is put on the table, preventing any table access by users. And during rebuilding a non-clustered index a shared table lock is put on the table, preventing all but SELECT operations to be performed on it.
REBUILD INDEX: Best performance, but places an exclusive table lock on the table, preventing any table access by users and shared table lock on the table, preventing all but SELECT operations to be performed on it.
Note: According to Microsoft best practices, index defragmentation is most effective when an index has at least 8 pages. DBCC INDEXDEFRAG is one of the deprecated command. The equivalent contemporary command is ALTER INDEX REORGANIZE
Here is an award winning solution for the SQL Server Indexes and Statistics maintenance. You can download IndexOptimize procedure and use it as a comprehensive solution for this purpose. The SQL Server Maintenance Solution website seems to me like a must have for all DBA’s. Following is the syntax for rebuilding or reorganizing indexes with fragmentation on all user databases
EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30