本文共 1904 字，大约阅读时间需要 6 分钟。

CREATE PROC SHANE_AutoProIndex  AS  DECLARE @tblName VARCHAR(40)  DECLARE @indexID INT DECLARE @proFlag FLOAT DECLARE @indexName VARCHAR(40)  DECLARE @sql varchar(200)   DECLARE _tblCur CURSOR FOR SELECT TblName FROM AutoProIndexModel  OPEN _tblCur  FETCH NEXT FROM _tblCur INTO @tblName  WHILE @@FETCH_STATUS = 0  BEGIN PRINT 'Now is Proing: ' + @tblName  --PRINT @tblName    DECLARE _indexCur CURSOR FOR SELECT index_id, avg_fragmentation_in_percent    FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'), OBJECT_ID(@tblName), NULL, NULL, 'LIMITED')      OPEN _indexCur   FETCH NEXT FROM _indexCur INTO @indexID, @proFlag    WHILE @@FETCH_STATUS = 0   BEGIN    PRINT 'The index id is: ' + CAST(@indexID AS VARCHAR(10)) + ', avg_fra_in_percent is: ' + CAST(@proFlag AS VARCHAR(20))     IF @proFlag > 5 AND @proFlag < 30      BEGIN      SELECT @indexName = name FROM sys.indexes WHERE [object_id] = OBJECT_ID(@tblName) AND index_id = @indexID       print @indexName + ' must be  REORGANIZE'      SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @tblName + ' REORGANIZE'      EXEC(@sql)  PRINT @SQL      END    ELSE IF @proFlag > 30      BEGIN      SELECT @indexName = name FROM sys.indexes WHERE [object_id] = OBJECT_ID(@tblName) AND index_id = @indexID       print @indexName + ' must be  REBUILD'      SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @tblName + ' REBUILD'      EXEC(@sql)  PRINT @SQL      END    FETCH NEXT FROM _indexCur INTO @indexID, @proFlag   END     CLOSE _indexCur   DEALLOCATE _indexCur   print ''  FETCH NEXT FROM _tblCur INTO @tblName  END  CLOSE _tblCur  DEALLOCATE _tblCur   该PROC中有张表AutoProIndexModel，这张表里面存储的是需要维护索引的几张表名。该PROC流程如下：1.先使用游标读取AutoProIndexModel中的需要整理的表的信息，进行循环2.使用DMF，sys.dm_db_index_physical_stats得出每张表中每个索引的碎片情况后，根据avg_fragmentation_in_percent 字段的值进行具体的操作3.如果avg_fragmentation_in_percent 在5-30之间进行索引重新组织，>30则索引重建。新建个计划任务后，定时调用该存储过程就可以实现索引的自动维护了。

转载地址：http://euymf.baihongyu.com/