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) as TableName, ind.name as IndexName, Indexstats.index_type_desc as Indextype, Indexstats.avg_fragmentation_ In_percent from Sys.dm_db_index_physical_stats (db_id (), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind
   
    on ind.object_id = indexstats.object_id and ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_ Percent > ORDER by Indexstats.avg_fragmentation_in_percent DESC
   

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.

For index fragmentation of Indextype HEAP, simply create a clustered index for the corresponding table, and the index fragmentation will automatically disappear.

For a table with more than 6 million records, either clustered or nonclustered, as long as the Rebuild operation is performed, all queries to the table are blocked, and only Reorganize is used, and the Reorganize operation on the clustered index of the table takes 26 minutes.

SQL Server index defragmentation actual operation record