先直接上代码：
1. CREATE PROC SHANE_AutoProIndex
2. AS
3.
4. DECLARE @tblName VARCHAR(40)
5. DECLARE @indexID INT
6. DECLARE @proFlag FLOAT
7. DECLARE @indexName VARCHAR(40)
8. DECLARE @sql varchar(200)
9.
10. DECLARE _tblCur CURSOR FOR SELECT TblName FROM AutoProIndexModel
11. OPEN _tblCur
12. FETCH NEXT FROM _tblCur INTO @tblName
13. WHILE @@FETCH_STATUS = 0
14. BEGIN
15. PRINT 'Now is Proing: ' + @tblName
16. --PRINT @tblName
17.
18. DECLARE _indexCur CURSOR FOR SELECT index_id, avg_fragmentation_in_percent
19. FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'), OBJECT_ID(@tblName), NULL, NULL, 'LIMITED')
20.
21. OPEN _indexCur
22. FETCH NEXT FROM _indexCur INTO @indexID, @proFlag
23.
24. WHILE @@FETCH_STATUS = 0
25. BEGIN
26. PRINT 'The index id is: ' + CAST(@indexID AS VARCHAR(10)) + ', avg_fra_in_percent is: ' + CAST(@proFlag AS VARCHAR(20))
27. IF @proFlag > 5 AND @proFlag < 30
28. BEGIN
29. SELECT @indexName = name FROM sys.indexes WHERE [object_id] = OBJECT_ID(@tblName) AND index_id = @indexID
30. print @indexName + ' must be REORGANIZE'
31. SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @tblName + ' REORGANIZE'
32. EXEC(@sql)
33. PRINT @SQL
34. END
35. ELSE IF @proFlag > 30
36. BEGIN
37. SELECT @indexName = name FROM sys.indexes WHERE [object_id] = OBJECT_ID(@tblName) AND index_id = @indexID
38. print @indexName + ' must be REBUILD'
39. SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @tblName + ' REBUILD'
40. EXEC(@sql)
41. PRINT @SQL
42. END
43. FETCH NEXT FROM _indexCur INTO @indexID, @proFlag
44. END
45.
46. CLOSE _indexCur
47. DEALLOCATE _indexCur
48.
49. print ''
50. FETCH NEXT FROM _tblCur INTO @tblName
51. END
52.
53. CLOSE _tblCur
54. DEALLOCATE _tblCur
55.
该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则索引重建。
新建个计划任务后，定时调用该存储过程就可以实现索引的自动维护了。
本文出自 “怒放的生命” 博客