SQLServer索引碎片查询及处理

1：查询索引碎片

?

1 2 3 4 5 6 7 8 9 10 11

SELECT OBJECT_NAME(ips.object_id) AS TableName,        ips.index_id,        name AS IndexName,        avg_fragmentation_in_percent,        DB_NAME(ips.database_id) AS DatabaseName FROM sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), NULL, NULL, NULL, NULL) AS ips     INNER JOIN sys.indexes AS si         ON si.object_id = ips.object_id            AND si.index_id = ips.index_id WHERE ips.avg_fragmentation_in_percent > 0       AND si.index_id <> 0;

　　
avg_fragmentation_in_percent是索引碎片率，低于5%可以不考虑。5%-30%建议reorganize，超过30%建议rebuild

2：reorganize

alter index [index_name] on dbo.[表名] reorganize;

3：rebuild

alter index [index_name] on dbo.[表名] rebuild;

4：rebuild all 重建表上所有索引

alter index all on dbo.[表名] rebuild;

?

1	<em id="__mceDel"><br><br></em>