参考sys.dm_db_index_physical_stats
检查索引碎片情况

1. 1.SELECT
   
2. 2.OBJECT_NAME(object_id) as objectname,
   
3. 3.object_id AS objectid,
   
4. 4.index_id AS indexid,
   
5. 5.partition_number AS partitionnum,
   
6. 6.avg_fragmentation_in_percent AS fra
   
7. 7.FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED')
   
8. 8.WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
   
9. 9.
   
10. 10.使用脚本中的 sys.dm_db_index_physical_stats 重新生成或重新组织索引 （来源于联机帮助）
    
11. 11.
    
12. 12.SET NOCOUNT ON;
    
13. 13.DECLARE @objectid int;
    
14. 14.DECLARE @indexid int;
    
15. 15.DECLARE @partitioncount bigint;
    
16. 16.DECLARE @schemaname nvarchar(130);
    
17. 17.DECLARE @objectname nvarchar(130);
    
18. 18.DECLARE @indexname nvarchar(130);
    
19. 19.DECLARE @partitionnum bigint;
    
20. 20.DECLARE @partitions bigint;
    
21. 21.DECLARE @frag float;
    
22. 22.DECLARE @command nvarchar(4000);
    
23. 23.– Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
    
24. 24.– and convert object and index IDs to names.
    
25. 25.SELECT
    
26. 26.object_id AS objectid,
    
27. 27.index_id AS indexid,
    
28. 28.partition_number AS partitionnum,
    
29. 29.avg_fragmentation_in_percent AS frag
    
30. 30.INTO #work_to_do
    
31. 31.FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED')
    
32. 32.WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
    
33. 33.– Declare the cursor for the list of partitions to be processed.
    
34. 34.DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
    
35. 35.– Open the cursor.
    
36. 36.OPEN partitions;
    
37. 37.– Loop through the partitions.
    
38. 38.WHILE (1=1)
    
39. 39.BEGIN;
    
40. 40.FETCH NEXT
    
41. 41.FROM partitions
    
42. 42.INTO @objectid, @indexid, @partitionnum, @frag;
    
43. 43.IF @@FETCH_STATUS < 0 BREAK;
    
44. 44.SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
    
45. 45.FROM sys.objects AS o
    
46. 46.JOIN sys.schemas as s ON s.schema_id = o.schema_id
    
47. 47.WHERE o.object_id = @objectid;
    
48. 48.SELECT @indexname = QUOTENAME(name)
    
49. 49.FROM sys.indexes
    
50. 50.WHERE object_id = @objectid AND index_id = @indexid;
    
51. 51.SELECT @partitioncount = count (*)
    
52. 52.FROM sys.partitions
    
53. 53.WHERE object_id = @objectid AND index_id = @indexid;
    
54. 54.– 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    
55. 55.IF @frag < 30.0
    
56. 56.SET @command = N‘ALTER INDEX ‘ + @indexname + N‘ ON ‘ + @schemaname + N‘.' + @objectname + N‘ REORGANIZE';
    
57. 57.IF @frag >= 30.0
    
58. 58.SET @command = N‘ALTER INDEX ‘ + @indexname + N‘ ON ‘ + @schemaname + N‘.' + @objectname + N‘ REBUILD';
    
59. 59.IF @partitioncount > 1
    
60. 60.SET @command = @command + N‘ PARTITION=' + CAST(@partitionnum AS nvarchar(10));
    
61. 61.EXEC (@command);
    
62. 62.PRINT N‘Executed: ‘ + @command;
    
63. 63.END;
    
64. 64.– Close and deallocate the cursor.
    
65. 65.CLOSE partitions;
    
66. 66.DEALLOCATE partitions;
    
67. 67.– Drop the temporary table.
    
68. 68.DROP TABLE #work_to_do;
    
69. 69.GO

复制代码

来源：https://www.jb51.net/article/51913.htm
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！