注册
登录
| 关键词： nbsp tblName DECLARE indexCur proFlag tblCur 索引 VARCHAR indexID FETCH |
先直接上代码：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://shane.blog.51cto.com/824878/634508 |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
