sql-server - 插入后需要重组或重新创建索引？

Question

我已经将一百万行插入到一个表中(在 sql 2000 上)，该表中已经有几百万行。目标表上有索引。它在新插入之前没有被删除。那么，每次新插入后，我是否必须重建或重组目标表的索引？或者 SQL Server 2000 是否已经在加载后执行了索引的自动排列？？

Dbas/Sql 专家请回复..这些索引让我很困惑

Answer 1

是的，您应该重新组织它，因为在如此巨大的插入之后，您的索引将变得支离 splinter 。

要检查碎片百分比，您可以这样做:-

 DBCC SHOWCONTIG

或

 select i.name,avg_fragmentation_in_percent 
 from sys.dm_db_index_physical_stats(db_id(),null,null,null,null) 
 S inner join sys.indexes I on s.object_id=I.object_id and s.index_id=i.index_id

检查这个site

Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.