SQL Server索引碎片整理实际操作记录

时间:2021-01-03 21:49:50

SQL Server 版本是 2008 R2。

查询数据库索引碎片情况的 SQL 语句(来源):

SQL Server索引碎片整理实际操作记录
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 > 50
ORDER BY indexstats.avg_fragmentation_in_percent DESC
SQL Server索引碎片整理实际操作记录

索引碎片整理的四种方法:

1)删除索引并重建

2)使用 DROP_EXISTING 语句重建索引

3)使用 ALTER INDEX REBUILD 语句重建索引

4)使用 ALTER INDEX REORGANIZE 重新组织索引

详见:SQL Server索引的维护 - 索引碎片、填充因子 <第三篇>

我们使用的是方法三 Rebuild 与方法四 Reorganize 。

对有些数据记录比较多、即使访问低峰也被经常访问的表进行索引 Rebuild 会失败,尝试删除索引也失败,出现 "Lock request time out period exceeded." 的错误,后来改用 Reorganize 操作成功。在对有些表的索引进行 Reorganize 操作时出现了 SQL Server Management Stuido 窗口一直卡死的问题,后来通过 Windows 任务管理器强制结束了这个窗口,结束后发现索引碎片已成功整理。

对于 IndexType 为 HEAP 的索引碎片,只需为对应的表创建聚集索引,索引碎片就会自动消失。

对于记录数超过600万的表,无论是聚集索引还是非聚集索引,只要进行 Rebuild 操作,就会阻塞所有对该表查询操作,只能使用 Reorganize ,对该表的聚集索引进行 Reorganize 操作耗时 26 分钟。