SQL SERVER 2005索引自动维护

奥尔覅几22

　　先直接上代码：
　　

• 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则索引重建。
　　新建个计划任务后，定时调用该存储过程就可以实现索引的自动维护了。