欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL SERVER 2008 R2 重建索引的方法

程序员文章站 2023-12-24 17:05:03
参考sys.dm_db_index_physical_stats 检查索引碎片情况 1.select 2.object_name(object_id) as...

参考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

上一篇:

下一篇: