SQL Server碎片分析——《SQL Server2008查询性能优化》笔记

白森

　　SQL Server中一个页面包含8KB数据，连续的8个页面组成一个区（extent）。数据的insert和update可以引起页面分割产生碎片。如果分割后的两个页面在同一个区内，这种碎片称为内部碎片，如果分割后的两个页面处于不同的区内，这种碎片称为外部碎片。
　　在索引的叶子页面内的数据总是有序的。假如一个页面内有键值为10,20,30,40四条记录而且此页面已经被装满了。现在要把键值为25的记录插进来，只能插入20和30之间。于是导致此页面分割，产生一个新页面，原页面的一部分数据将被移动到这个新页面内。如果产生的这个新页面和原来的页面在同一个区内，那么就是内部碎片，否则，产生的就是外部碎片。
　　1、  碎片成因
　　1）  update语句引起的的页面分割
　　为了测试update语句引起页面分割，用以下脚本生成测试数据




USE AdventureWorks;
GO
IF (SELECT  OBJECT_ID('dbo.t1')
   ) IS NOT NULL
    DROP TABLE dbo.t1;
GO
CREATE TABLE dbo.t1 (c1 INT, c2 CHAR(999),c3 VARCHAR(10));
INSERT  INTO dbo.t1 VALUES  (100, 'c2','');
INSERT  INTO dbo.t1 VALUES  (200, 'c2','');
INSERT  INTO dbo.t1 VALUES  (300, 'c2','');
INSERT  INTO dbo.t1 VALUES  (400, 'c2','');
INSERT  INTO dbo.t1 VALUES  (500, 'c2','');
INSERT  INTO dbo.t1 VALUES  (600, 'c2','');
INSERT  INTO dbo.t1 VALUES  (700, 'c2','');
INSERT  INTO dbo.t1 VALUES  (800, 'c2','');
CREATE CLUSTERED INDEX i1 ON dbo.t1 (c1);
GO
　　聚簇索引在叶子页面内的平均尺寸（不包括内部开销）、
　　=[c1]平均尺寸+[c2]平均尺寸+[c3]平均尺寸
　　=INT的尺寸+CHAR(999)的尺寸+[c3]中数据的平均尺寸
　　=4+999+0=1003字节
　　SQL Server中行的最大尺寸为8060字节。因此，在内部开销不高的情况下，测试表内的8行数据可以容纳在一个8KB页面内。
　　可以用以下脚本查看sys.dm_db_index_physical_stats的相关信息




select s.avg_fragmentation_in_percent,s.fragment_count,s.page_count,
s.avg_page_space_used_in_percent,s.record_count,
s.avg_record_size_in_bytes
from sys.dm_db_index_physical_stats(db_id('adventureworks'),
object_id(N'dbo.t1'), null,null,'Sampled') as s
　　执行后的结果如下：

　　从这个结果可以看出分配给聚簇索引的页面数量page_count为1，每行的平均字节为1010，
　　所用的平均空间avg_page_space_used_in_percent=100。可以看出此页面已经没有空闲空间可以容纳C3的内容，C3当前为空。
　　下面用以下脚本更新C3字段的内容，理论上应该发生分页




update t1 set c3='add data'
where c1=200
　　再次运行脚本查看sys.dm_db_index_physical_stats的信息

　　从执行结果来看已经产生碎片，页面数量page_count已经为2。每个页面的平均使用空间大约为50%。
　　2）  INSERT语句引起的页面分割
　　重新运行测试脚本生成测试数据。因为页面已经没有空闲空间了，所以任何添加中间行的企图都将导致叶子页面的分割。




insert into dbo.t1 values(110,'c2','')
　　执行后，查看sys.dm_db_index_physical_stats的输出来验证，

　　2、  碎片开销
　　内部碎片和外部碎片对数据检索性能都有负面影响。外部碎片导致磁盘上的索引页面不连续，新的叶子页面和原始叶子页面离得很远，物理顺序与逻辑顺序不同。为了更好的性能，首选顺序IO，因为这能在一个磁盘IO读取整个区（8和8KB页面）。非连续的页面需要非顺序或者随机IO操作来从磁盘读取数据，一个随机IO只能读取一个页面（8KB）。
　　内部碎片的产生使数据稀疏的分布在大量的页面中，这增加了读取页面到内存中所需的磁盘IO操作而且增加了从内存中检索数据的逻辑读取数量。
　　用以下脚本测试碎片开销




IF (SELECT  OBJECT_ID('t1')
   ) IS NOT NULL
    DROP TABLE t1
GO
CREATE TABLE t1
    (c1 INT
    ,c2 INT
    ,c3 INT
    ,c4 CHAR(2000))
CREATE CLUSTERED INDEX i1 ON t1 (c1) ;
WITH    Nums
          AS (SELECT    1 AS n
              UNION ALL
              SELECT    n + 1
              FROM      Nums
              WHERE     n < 21
             )
    INSERT  INTO t1 (c1, c2, c3, c4)
            SELECT  n
                   ,n
                   ,n
                   ,'a'
            FROM    Nums ;
WITH    Nums
          AS (SELECT    1 AS n
              UNION ALL
              SELECT    n + 1
              FROM      Nums
              WHERE     n < 21
             )
    INSERT  INTO t1 (c1, c2, c3, c4)
            SELECT  41 - n
                   ,n
                   ,n
                   ,'a'
            FROM    Nums
　　而后执行以下语句




select * from t1 where c1 between 21 and 25--读取6行
　　查询开销如下
　　表't1'。扫描计数1，逻辑读取6 次
　　CPU 时间= 0 毫秒，占用时间= 52 毫秒
　　执行下面语句




select * from t1 where c1 between 1 and 40--读取所有行
　　查询开销如下
　　表't1'。扫描计数1，逻辑读取15 次
　　CPU 时间= 0 毫秒，占用时间= 69 毫秒
　　为了评估索引碎片化数据集对逻辑读取的影响，重建聚簇索引以重新物理排列索引叶子页面。
　　



alter index i1 on dbo.t1 rebuild

　　重新执行以上两个查询开销如下
　　表't1'。扫描计数1，逻辑读取5 次
　　CPU 时间= 0 毫秒，占用时间= 45 毫秒
　　表't1'。扫描计数1，逻辑读取13 次
　　CPU 时间= 0 毫秒，占用时间= 51 毫秒
　　可以看到逻辑读取已经分别降到5和13。
　　3、  碎片解决方法
　　1）  卸载（drop index）并重建索引
　　2）  使用DROP_EXISTING子句重建索引
　　如：
　　Create unique clustered index pkc1
on t1(c1)
　　With (drop_existing=on)
　　3）执行alter index rebuild 语句重建索引
　　4）执行 alter index reorganize语句重建索引
　　4、 填充因子
　　因为叶子页面被填满数据，所以后来的数据更新或者插入中间键值的数据都有可能导致索引碎片的产生。SQL Server使用填充因子控制页面中空闲空间的数量。页面内保留一部分空闲空间，在一定程度上减少了插入或者更新数据产生索引碎片的机会。
　　使用以下脚本创建测试数据来揭示填充因子的重要性，
　　



IF (SELECT  OBJECT_ID('t1')
   ) IS NOT NULL
    DROP TABLE t1
GO
CREATE TABLE t1 (c1 INT, c2 CHAR(999)) ;
WITH    Nums
          AS (SELECT    1 AS n
              UNION ALL
              SELECT    n + 1
              FROM      Nums
              WHERE     n < 24
             )
    INSERT  INTO t1 (c1, c2)
            SELECT  n * 100
                   ,'a'
            FROM    Nums;
CREATE CLUSTERED INDEX i1 ON t1(c1);

　　这段脚本创建了24行数据，每行数据的平均尺寸为1010，所以理论上需要3个页面，
　　运行脚本查看sys.dm_db_index_physical_stats的信息

　　为了避免插入或者更新操作产生碎片，可以用一填充因子重建聚簇索引来使页面保留一定的空闲空间，
　　



alter index i1 on dbo.t1 rebuild
with(fillfactor=75)

　　运行此脚本后，叶子页面的数量将增加到4，运行脚本查看sys.dm_db_index_physical_stats的信息

　　从输出信息可以看到avg_page_space_used_in_percent大约为75%和设置的填充因子基本相同。这样可以使每个页面多插入两条数据而不会引起页面分割。可以添加2行到第一个页面（已经存在的数据C1字段值为100~600,6行）来测试
　　



insert into t1 values(110,'a')
insert into t1 values(120,'a')

　　而后运行脚本查看sys.dm_db_index_physical_stats的信息

　　可以看到page_count还是4，此时，第一个页面已经8行，页面已经没有空闲空间了，所以在第一个页面内再增加一条记录，将引起页面分割
　　



insert into t1 values(130,'a')

　　执行后查看sys.dm_db_index_physical_stats的信息

　　这次page_count=5，尽管填充因子是75%，但是此时的avg_page_space_used_in_percent大约为67.5%，可以这样计算出该值
　　平均每页行数/最大每页行数=（27/5）/8=67.5%
　　从这个例子中可以看出填充因子在随着数据的修改，已经不再重要了。因此，如果使用非默认的填充因子，应该使用指定填充因子定期的重建索引或者alter index reorganize或者alter index rebuild。一般来说默认填充因子已经很好了，只有写操作达到读操作的较大比例（大于30%）时才考虑指定填充因子。