SQL SERVER 2008 R2 重建索引的方法

参考sys.dm_db_index_physical_stats

检查索引碎片情况

1.select
2.object_name(object_id) as objectname,
3.object_id as objectid,
4.index_id as indexid,
5.partition_number as partitionnum,
6.avg_fragmentation_in_percent as fra
7.from sys.dm_db_index_physical_stats (db_id(), null, null , null, ‘limited')
8.where avg_fragmentation_in_percent > 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(), null, null , null, ‘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