MSSQL 2005 提供了一个动态管理函数 sys.dm_db_index_physical_stats,可以方便直观地查看到指定表或视图的数据和索引的大小和碎片信息。 下面这条语句,可以查看当前数据库中所有索引的碎片情况: SELECT object_name(a.object_id) [TableName] ,a.index_id ,name [IndexName] ,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID() , NULL , NULL, NULL, NULL ) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id 下面这条语句重建索引: ALTER INDEX ALL ON [dbo].[YourTableName] REBUILD WITH ( SORT_IN_TEMPDB = ON ,STATISTICS_NORECOMPUTE = ON ,ONLINE = ON); |