索引碎片

  碎片在表中的数据被修改时产生。当插入或者更新表中的数据时,表的对应聚簇索引和受到影响的聚簇索引被修改。如果对索引的修改不能容纳于同一页面中,可能导致索引叶子页面分割。一个新的叶子页面将被添加以包含原来的部分,并且维持索引键中行的逻辑顺序。虽然新的叶子页面维护原始页面中行的逻辑顺序,但是这个新的页面通常在磁盘上与原来页面相邻。或者,索引逻辑关键字顺序与文件中的物理顺序不相同。

  页面被集合成更大的单元---区。Sql server使用一个区作为磁盘上的物理分配单元。在理想状态下,包含一个索引的叶子页面的区的物理顺序应该和索引的逻辑顺序相同。这减少了检索一定范围的索引行时所需要的区之间的切换次数。但是,页面分割可能物理上打乱区中的页面顺序,也可能从物理上打乱区本身的顺序。

  有了分布在两区的叶子页面,理想状态下,预期使用最多一次两区之间切换就可以读取一定范围的索引行。但是,区之间无组织的页面可能导致在读取一定范围的行数时超过一次的区切换。这种碎片被称为外部碎片。

  数据操作引起的页面分割已经在第一个叶子页面中创建了一个空位,这被称为内部碎片。对于高事务性的数据库,特意在叶子页面中留下一些空闲空间很受欢迎的,这样添加新行,或者修改现有行的大小,而不造成页面分割。

  内部和外部碎片对数据检索性能都有负面影响。外部碎片导致磁盘上的索引页面不连续,新的叶子页面和原始叶子页面离的很远,物理顺序与逻辑顺序不同。因此,在索引上的一个范围扫描将需要比理想状态下更多的对应区之间的切换。内部碎片的情况下,行被稀疏的分散到大量的页面中,增加了读取索引页面到内存中所需的磁盘I/O操作,并且增加了从内存检索多个索引所需的逻辑读数量,即使增加了数据检索的开销,少数的内部碎片仍有可能有利,因为它使你在操作的时候不会引起页面分割,对于不必要穿越一系列页面检索数据的查询,碎片可能没有太大的影响。

解决碎片的方案:

可以通过重新排列索引行和页面使物理和逻辑顺序相符来解决索引碎片的问题,为了减少外部碎片,可以物理重排索引的叶子页面使其遵循索引的逻辑顺序。

  1. 卸载并重建索引
  2. 使用Drop_existing子句重建索引
  3. 在索引上执行ALTER INDEX REBULID语句
  4. 在索引上执行ALTER INDEX REORGANIZE语句

索引碎片整理脚本:

复制代码
 1 set nocount on
 2 
 3 declare @objectid         int
 4        ,@indexid          int
 5        ,@partitioncount   bigint
 6        ,@schemaname       sysname
 7        ,@objectname       sysname
 8        ,@indexname        sysname
 9        ,@partitionnum     bigint
10        ,@partitions       bigint
11        ,@frag             float
12        ,@command          varchar(1000)
13 
14 select 
15 objectid     = object_id
16       ,indexid      = index_id
17       ,partitionnum = partition_number
18       ,frag         = avg_fragmentation_in_percent
19   into #work_to_do
20   from sys.dm_db_index_physical_stats(db_id(), null, null , null, 'LIMITED')
21 where avg_fragmentation_in_percent > 10.0
22    and index_id > 0
23 
24 -- declare the cursor for the list of partitions to be processed.
25 declare partitions cursor for
26    select * from #work_to_do
27 
28 -- Open the cursor.
29 open partitions
30 
31 -- Loop through the partitions.
32 fetch next from partitions into @objectid, @indexid, @partitionnum, @frag
33 
34 while @@fetch_status = 0 begin
35    select @objectname = o.name, @schemaname = s.name
36      from sys.objects as o
37             inner join sys.schemas as s
38       on s.schema_id = o.schema_id
39    where o.object_id = @objectid
40 
41    select @indexname = name
42      from sys.indexes
43     where object_id = @objectid
44       and index_id = @indexid
45 
46    select @partitioncount = count (*)
47     from sys.partitions
48    where object_id = @objectid
49      and index_id = @indexid
50 
51    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
52    if @frag < 30.0 begin
53        select @command = 'alter index ' + @indexname + ' on ' 
54                        + @schemaname + '.' + @objectname + ' reorganize'
55 
56        if @partitioncount > 1
57            select @command = @command + ' partition=' + convert(char, @partitionnum)
58    end
59 
60    if @frag >= 30.0 begin
61        select @command = 'alter index ' + @indexname +' on ' 
62                        + @schemaname + '.' + @objectname + ' rebuild'
63 
64        if @partitioncount > 1
65            select @command = @command + ' partition=' + convert(char, @partitionnum)
66    end
67 
68    -- exec (@command)
69    print 'Executed: ' + @command
70 
71    fetch next from partitions into @objectid, @indexid, @partitionnum, @frag
72 end
73 
74 -- free resource
75 close partitions
76 deallocate partitions
77 drop table #work_to_do
复制代码

 

posted @   王凯旋  阅读(408)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示