SQL SERVER 2005索引自动维护
先直接上代码：[*]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(@tblName) AND index_id = @indexID
[*] print @indexName + ' must beREORGANIZE'
[*] 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(@tblName) AND index_id = @indexID
[*] print @indexName + ' must beREBUILD'
[*] 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则索引重建。
新建个计划任务后，定时调用该存储过程就可以实现索引的自动维护了。
页:
[1]