SQL Server index defragmentation actual operation record

The SQL Server version is R2.

SQL statement (source) for querying database index fragmentation conditions:

SELECT object_name(Ind.)object_id) asTableName, Ind.name asIndexName, Indexstats.index_type_desc asIndextype, Indexstats.avg_fragmentation_in_percent fromSys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,NULL) IndexstatsINNER JOINsys.indexes Ind onInd.object_id =Indexstats.object_id  andind.index_id=indexstats.index_idWHEREIndexstats.avg_fragmentation_in_percent>  - ORDER  byIndexstats.avg_fragmentation_in_percentDESC

Four ways to defragment an index:

1) Delete index and rebuild

2) Rebuilding the index with the DROP_EXISTING statement

3) Rebuilding the index with the ALTER index REBUILD statement

4) Reorganize index with ALTER index REORGANIZE

See: Maintenance of SQL Server Indexes-index fragmentation, fill factor < third >

We are using method three Rebuild with method four Reorganize.

For some data records, even if access to the low peak is also frequently accessed by the table index Rebuild will fail, attempt to delete the index also failed, "Lock request time out period exceeded." Error, and then use Reorganize operation succeeded. The SQL Server Management stuido window has been stuck while the index for some tables has been Reorganize, and the Windows Task Manager later forced the window to end, and the index fragmentation was found to have been successfully collated.

SQL Server index defragmentation actual operation record