08r2系统服务器开索引,SQL SERVER 2008 R2 重建索引的方法

参考sys.dm_db_index_physical_stats

检查索引碎片情况

10.0 AND index_id > 0;

9.

10.使用脚本中的 sys.dm_db_index_physical_stats 重新生成或重新组织索引 (来源于联机帮助)

11.

12.SET NOCOUNT ON;

13.DECLARE @objectid int;

14.DECLARE @indexid int;

15.DECLARE @partitioncount bigint;

16.DECLARE @schemaname nvarchar(130);

17.DECLARE @objectname nvarchar(130);

18.DECLARE @indexname nvarchar(130);

19.DECLARE @partitionnum bigint;

20.DECLARE @partitions bigint;

21.DECLARE @frag float;

22.DECLARE @command nvarchar(4000);

23.– Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

24.– and convert object and index IDs to names.

25.SELECT

26.object_id AS objectid,27.index_id AS indexid,28.partition_number AS partitionnum,29.avg_fragmentation_in_percent AS frag

30.INTO #work_to_do

31.FROM sys.dm_db_index_physical_stats (DB_ID(),‘LIMITED')

32.WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

33.– Declare the cursor for the list of partitions to be processed.

34.DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

35.– Open the cursor.

36.OPEN partitions;

37.– Loop through the partitions.

38.WHILE (1=1)

39.BEGIN;

40.FETCH NEXT

41.FROM partitions

42.INTO @objectid,@indexid,@partitionnum,@frag;

43.IF @@FETCH_STATUS < 0 BREAK;

44.SELECT @objectname = QUOTENAME(o.name),@schemaname = QUOTENAME(s.name)

45.FROM sys.objects AS o

46.JOIN sys.schemas as s ON s.schema_id = o.schema_id

47.WHERE o.object_id = @objectid;

48.SELECT @indexname = QUOTENAME(name)

49.FROM sys.indexes

50.WHERE object_id = @objectid AND index_id = @indexid;

51.SELECT @partitioncount = count (*)

52.FROM sys.partitions

53.WHERE object_id = @objectid AND index_id = @indexid;

54.– 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

55.IF @frag < 30.0

56.SET @command = N‘ALTER INDEX ‘ + @indexname + N‘ ON ‘ + @schemaname + N‘.' + @objectname + N‘ REORGANIZE';

57.IF @frag >= 30.0

58.SET @command = N‘ALTER INDEX ‘ + @indexname + N‘ ON ‘ + @schemaname + N‘.' + @objectname + N‘ REBUILD';

59.IF @partitioncount > 1

60.SET @command = @command + N‘ PARTITION=' + CAST(@partitionnum AS nvarchar(10));

61.EXEC (@command);

62.PRINT N‘Executed: ‘ + @command;

63.END;

64.– Close and deallocate the cursor.

65.CLOSE partitions;

66.DEALLOCATE partitions;

67.– Drop the temporary table.

68.DROP TABLE #work_to_do;

69.GO

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。

如您喜欢寻找一群志同道合、互帮互助的学习伙伴，可以点击下方链接加入：

编程之家官方1群

编程之家官方2群

编程之家官方3群

编程之家官方4群