您的位置:新葡亰496net > 网络数据库 > DML和目录内部结构变化,碎片查看与化解方案

DML和目录内部结构变化,碎片查看与化解方案

发布时间:2019-06-16 03:54编辑:网络数据库浏览(137)

    一.概述

        索引填充因子作用:提供填充因子选项是为了优化索引数据存储和性能。 当创建或重新生成索引时,填充因子的值可确定每个叶级页上要填充数据的空间百分比,以便在每一页上保留一些剩余存储空间作为以后扩展索引的可用空间,例如:指定填充因子的值为 80 表示每个叶级页上将有 20% 的空间保留为空,以便随着向基础表中添加数据而为扩展索引提供空间。

      填充因子的值是 1 到 100 之间的百分比,服务器范围的默认值为 0,这表示将完全填充叶级页。

     1.1 页拆分现象

       根据数据的查询和修改的比例,正确选择填充因子值,可提供足够的空间,以便随着向基础表中添加数据而扩展索引,从而降低页拆分的可能性。如果向已满的索引页添加新行(新行位置根据键排序规则,可以是页中任意行位置), 数据库引擎将把大约一半的行移到新页中,以便为该新行腾出空间。 这种重组称为页拆分。页拆分可为新记录腾出空间,但是执行页拆分可能需要花费一定的时间,此操作会消耗大量资源。 此外,它还可能造成碎片,从而导致 I/O 操作增加。 如果经常发生页拆分(可能过sys.dm_db_index_physical_stats 来查看页拆分情况),可通过使用新的或现有的填充因子值来重新生成索引,从而重新分发数据。

      填充值设置过低: 优点是 插入或修改时降低页的拆分次数。缺点是 会使索引需要更多的存储空间,并且会降低读取性能。

      填充值设置过高: 优点是 如果每一个索引页数据都全部填满,此时select效率最高。缺点是 插入或修改时需要移动后面所有页,效率低。

    索引碎片:

    索引已经是性能优化中大家常常提到而说到的问题,关于索引的很多的概念和解析,我们站点的索引进阶系列文章已经做了比较全面的讲述,我们这里就不在重复了。

    一 . dm_db_index_physical_stats 重要字段说明

      1.1 内部碎片:是avg_page_space_used_in_percent字段。是指页的填充度,为了使磁盘使用状况达到最优,对于没有很多随机插入的索引,此值应接近 100%。 但是,对于具有很多随机插入且页很满的索引,其页拆分数将不断增加。 这将导致更多的碎片。 因此,为了减少页拆分,此值应小于 100%。

      1.2 外部碎片:也叫逻辑碎片是avg_fragmentation_in_percent字段。是分页的逻辑顺序和物理顺序不匹配或者索引拥有的扩展不连续时产生。当对表中定义的索引进行数据修改(INSERT、UPDATE 和 DELETE 语句)的整个过程中都会出现碎片。 由于这些修改通常并不在表和索引的行中平均分布,所以每页的填充度会随时间而改变。 对于扫描表的部分或全部索引的查询,这种碎片会导致额外的页读取。 这会妨碍数据的并行扫描。

      1.3 使用查看dm_db_index_physical_stats索引碎片 (SQL server 2005以上)。

    SELECT OBJECT_NAME(sys.indexes.OBJECT_ID) AS tableName,
     sys.indexes.name,   
     page_count,
     (page_count*8.0)AS 'IndexSizeKB',
     avg_page_space_used_in_percent,
     avg_fragmentation_in_percent,
     record_count,avg_record_size_in_bytes,
    index_type_desc,
    fragment_count 
    from sys.dm_db_index_physical_stats(db_id('dbname'),object_id('tablename'), null,null,'sampled') 
     JOIN sys.indexes  ON   sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id
     AND sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id
    

        下面还是接着上一篇查询PUB_StockCollect表下的索引

    图片 1

      (1) avg_fragmentation_in_percent(外部碎片也叫逻辑碎片):最重要的列,索引碎片百分比。
        val >10% and val<= 30% -------------索引重组(碎片整理) alter index reorganize )
        val >30% --------------------------索引重建 alter index rebulid with (online=on)
        avg_fragmentation_in_percent:大规模的碎片(当碎片大于40%),可能要求索引重建
      (2) page_count:索引或数据页的总数。
      (3) avg_page_space_used_in_percent(内部碎片):最重要列:页面平均使用率也叫存储空间的平均百分比, 值越高(以80%填充度为参考点) 页存储数据就越多,内部碎片越少。
      (4) avg_record_size_in_bytes:平均记录大小(字节)。
      (5) index_type_desc列:索引类型-聚集索引或者非聚集索引等。
      (6) record_count:总记录数,相当于行数。
      (7) fragment_count: 碎片数。

    1.修改数据对索引结构的影响

    合适的索引对查询性能和效率的提升是巨大的,但是万事有利有弊,拥有索引的表在增、删、改记录时需要去维护索引。如何让增、删、改更快速更高效?这就需要了解数据修改时对索引结构会产生什么影响。

    二. 碎片与填充因子案例

       下面分析在生产环境下,对长时间一个表的ix_1索引进行分析。

    -- 有一个PUB_Search_ResultVersions2表长期有增删改操作, 在很长一段时间运行后,查看碎片如下
    dbcc SHOWCONTIG (PUB_Search_ResultVersions2,'ix_1')
    

      图片 2

        通过上图可以了解到平均页密度是29.74%,也就是内部碎片太多,现三个页的数据存储量才是正常一个页的存储量。扫描的页数是703页,涉及到了192个区。下面重新维护索引

    --重建索引
    ALTER INDEX ix_1 ON dbo.PUB_Search_ResultVersions2 REBUILD
    

      图片 3

         通过上图可以看到,扫描页数只有了248页(原来是703页) 用了36区(原来是192个区),现等于一页的实际数据是之前三页的总量, 查询将会减少了大量的I/O扫描。

      如果频繁的增删改,最好设置填充因子,默认是0,也就是100%, 如果有新索引键排序后,挤入到一个已填满8060字节的页中时,就会发生页拆分,产生碎片,这里我使用图形界面来设置填充因子为85%(最好通过t-sql来设置,做运维自动维护),再重建下索引使设置生效。

      图片 4

      下图可以看出平均页密度是85%,填充因子设置生效。可以在通过sys.dm_db_index_physical_stats重新查看该索引页使用数量。

    图片 5

    • 内部碎片(或说叶级填充率):反映数据叶级的空间占用率或空闲率
    • 外部碎片:由于sqlserver以连续的8个page作为一个数据库块(区)extent作为读取单位,故此由于物理存储上的区和逻辑上不一致(不连续)而造成io读取切换

    我们都知道,对于索引而言,我们会面临两个问题:

    二. 解决碎片方法

    -------------sqlserver 2000 碎片解决--------------
    -- 索引重建 充填因子80
    dbcc dbreindex(PUB_StockCategory,'PK_PUB_StockCategory',80)
    -- 索引重组
    DBCC INDEXDEFRAG(dbname,PUB_StockCategory,'PK_PUB_StockCategory')
    

     

    ------------sqlserver 2005以上碎片解决--------
    -- 重新组织表中单个索引 
     ALTER INDEX ix_pub_stock_2 ON dbo.PUB_Stock REORGANIZE  
     -- 重新组织表中的所有索引
     ALTER INDEX ALL ON dbo.PUB_Stock REORGANIZE  
     -- 重新生成表中单个索引 (重点:重建索引用)
     ALTER INDEX ix_pub_stock_2 ON dbo.PUB_Stock REBUILD
     -- 重新生成表中的所有索引 
     ALTER INDEX ALL  ON dbo.PUB_Stock  
     REBUILD  WITH(FILLFACTOR=80, SORT_IN_TEMPDB=ON ,STATISTICS_NORECOMPUTE = ON )
    

    1.1页拆分和行移动现象

    1.页拆分

    页拆分也称为页分裂。当有序的页面容不下新记录时就会出现页拆分现象。页拆分时SQL Server会尽量将旧页的一半记录复制到新页,其中的动作是先在旧页delete需要移动的行再在新页insert移动的行,新插入的行会根据键值大小来决定插在旧页中还是新页中。

    INSERT和UPDATE都可能会导致页拆分。当页拆分后还是不能容下某记录时,会出现二次拆分,二次拆分后发现还是不能容下会三次拆分,直到能容下这部分记录。假如父页原有10行,插入一个7900字节的,第一次拆分差不多移动5行左右到新页,发现在新页还是容不下新行,又拆分移动2行到另一个新页,还是发现不能和新行并存,接着拆分2次,最后发现,新行只能独立成页才最后一次拆分页来存放新行,这时就有不少页只利用了很少一点空间。

    页拆分后的页之间通过双链表连接,即形成上下页的关系。页拆分会记录日志,并且在拆分完成后,页拆分的专属系统内部事务会单独被提交,因此即使INSERT语句回滚了,拆分的页也不会回滚。也因此,频繁页拆分是一个消耗大量资源的动作。

    页面容不下新记录时并不一定会页拆分,只有有序的页面会页拆分。如果是堆表的数据页,插入或更新记录都是“见缝插针”型的页填充,不会出现页拆分现象。如果新记录插入的位置是B树中某个层次的中间一个页面(如叶级层次的中间某页),当该页容不下新记录时,则一定会进行页拆分。如果新记录是插在最后一页(例如,具有IDENTITY属性的列为聚集键,向其中插入新记录时总是会插入在表尾),并且该页容不下新记录,则有两种情况:一是进行页拆分,所有的索引页(包括聚集的和非聚集的)和聚集索引叶级的第一页都是这种情况;二是直接分配新页存放新记录,不进行页拆分,聚集索引的叶级部分除了第一页的所有页都是这种情况。

    下面的图中演示了向聚集表尾插入数据的页拆分过程。随着数据不断插入到聚集表的尾部,叶级的第一页首先拆分,这时会分配第二个叶级页和一个根页,并将接近一半的记录移动到第二个叶级页中,以后将尽量完全填充叶级页。这也是聚集索引的一个作用,表尾数据的插入不会导致大量的页拆分,并且保证了叶级页的空间使用率。当第一个根页无法容纳新记录时,将分配一个新的中间页和一个新的根页,旧的根页则变成中间页,并且以后将一直分裂,页面的空间使用率也不高。

     图片 6

    需要引起注意的是,每当B树结构中出现一个新的层次页时,为这个新的层次分配的页码总是会挤在中间。例如,下面的图中所展示的情况,新分配的根页页码为257,挤在叶级第一页和第二页的页码中间。

     图片 7

    2.行移动

    行移动的现象只在更新行和页拆分的时候出现。行移动可能在本页移动,也可能在页间移动。

    页拆分时的行移动很容易理解,拆分时尽量将旧页的大概一半记录移动到新页,这是页间的行移动。

    那更新行时的行移动是怎么进行的呢?更新行时可能是在本页移动,可能是页间移动。不管在页内移动还是页间移动,移动后如何找到记录是问题的关键,这和记录是否有序、如何定位记录有关。

    对于有序的记录(所有的索引页和聚集索引的叶级页中的记录),通过顺序就可以找到移动后的位置。如果更新行时,行记录只需在本页移动,则只需重排下该页的slot,空间位置上不会真的移动这一行。例如,某聚集表的数据页中记录了聚集键值为1(slot0)、3(slot1)、5(slot2)、7(slot3)、9(slot4)的记录,如果将3更新为6,则该记录可以继续留在本页,只需重排下slot,重排后记录对应为1(slot0)、5(slot1)、6(slot2)、7(slot3)和9(slot4)。如果将3修改为4呢?那么除了修改键值外不做任何其它改变。如果更新行时,行记录需要移动到其它页上,这时先在旧页执行DELETE再在新页执行INSERT,当然,这里也会重排相关页内的slot。

    对于无序的记录,也就是堆表的数据页,如果记录在页间移动,则会在原记录处留下转发指针(forwarding pointer),用于定位移动后的位置。如果该记录需要二次移动,则会更新原记录处的转发指针指到最新的位置,而不会在中间的位置添加转发指针,即转发指针不可能指向另一个转发指针。转发指针的作用是用于定位,如果堆中有非聚集索引,只需让非聚集索引的叶级行定位器RID指向转发指针的位置,通过转发指针就能定位新位置。

    转发指针只在堆中出现,当转发指针数量多时,它对性能的影响非常大,可能出现多十倍甚至百倍的逻辑读。数据库收缩或文件收缩会收缩转发指针;当再次更新转发后的行记录使得原位置又可以容纳该行,则该行会复位并删除转发指针。

    堆中行的更新不会出现页内移动,因为只要本页空间够容下更新后的记录,该记录直接在本页上扩展空间即可。因此,除非物理移动了数据文件的位置,堆中非聚集索引行定位器RID将不会因为行的更新而受到影响。

     

    1.是否建立了合适的索引

    1.2 插入行

    堆中插入行,是“见缝插针”型。此时会寻找空间足够大的“缝”来插入这根“针”,如果有空“缝”但空间不够放这一行记录,则不会在这里插入;如果在已分配的页中没有“缝”可以存放记录,就新分配一个页来存放。由于总会找到合适的空间,因此不会出现页拆分现象。注意:更新行是DELETE和INSERT的结合操作,因此在堆表更新行时,即使容不下行也不会页拆分,而是留下转发指针。

    聚集表中插入行的位置是固定了的,页中容不下新记录时可能会出现页拆分,也可能不会页拆分,具体的情况在刚才的页拆分段落的上下文中说明了。

    在非聚集索引的索引页上插入记录且容纳不下时会出现页拆分。

    • 逻辑碎片:这是索引的叶级页中出错页所占的百分比。对于出错页,分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页
    • 区碎片:这是堆的叶级页中出错区所占的百分比。出错区是指:包含堆的当前页的区不是物理上的包含前一页的区后的下一个区。(微软真不会解释概念:(

    2.如何维护和诊断现有的索引

    1.3 删除行

    1.删除堆的数据页

    堆表数据删除后不释放空间,留下slot但slot不指向页中的位置,也就是像slot 0  0x0这样。这时候如果有新记录要存放就可以“见缝插针”,并将原来没有指向的slot指向这一插入的行。

    下面的图中展示的是某个堆的页中记录被删除后的偏移信息,删除的是原来slot 0到slot 6的记录。

     图片 8

    如果想要释放堆中的空间,可以使用TRUNCATE删除整个表中数据;或者在DELETE时加上WITH(TABLOCK)选项(如DELETE FROM WITH(TABLOCK) table_name WHERE...)来按页释放空间;也可以先在堆中建立聚集索引,然后删除数据再删除聚集索引。

    2.删除聚集表中记录

    聚集索引的叶级和聚集表中非聚集索引的叶级记录被删除后会在原位置留下虚影记录(ghost_record),它们不是真正的被删除,只是在记录上做了虚影标记。该标记可以从页的标头信息查看,看下图,图中只整理了某页与虚影记录相关的信息。虚影记录由后台进程定时清理,清理后空间被释放。

    图片 9

    因为叶级还有虚影,所以非叶级仍然需要指向它们,因此聚集索引的非叶级和聚集表中非聚集索引的非叶级记录都不会被删除,而且它们不是虚影,而是原原本本的原记录。直到后台进程清除虚影后,叶级页被释放,指针也被释放,当非叶级页上没有数据了也直接删除并释放空间。

    3.删除堆中非聚集索引的叶级和非叶级记录

    因为堆中非聚集索引的行定位器指向堆中行位置,因此删除堆中行的同时会释放指针并删除叶级页中对应的记录,如果删除的记录足够多,还会删除非叶级的记录。不过删除非聚集索引的叶级和非叶级会直接释放空间,而不是和删除堆的数据页一样仍然占用空间。

     

    对于第一个问题,这确实不是三言两语可以讲清楚的,因为这首先需要对索引的知识掌握的非常清楚,而且还要知道建立索引的表中的数据的使用的情况(如读写的频率等)。我们会在之后的“收费阅读”版块,对索引进行深度的剖析,希望大家关注。

    1.4 更新行

    更新行可能出现行移动和页拆分。行移动又可能是本页移动和页间移动,这种情况是非在位更新;还可能是原地更新,即不会出现任何移动,这种情况称为在位更新。

    更新行的具体内部变化已经在刚才的页拆分和行移动段落里分情况讨论了,这里就不赘述了。

    查询碎片情况:

    对于第二个问题,我们首先就要清楚:索引建立之后不是一劳永逸的,而是需要不断的维护,而且数据库中的数据是变化的,所以,此时的索引可以不适合或者需要进行一些处理,如重组等。

    2.碎片

    在SQL Server中,碎片分两种:内部碎片和外部碎片。

    1.内部碎片

    内部碎片一般还称为页密度或物理碎片,表示页中减去填充因子所占的空间后的空间使用率,也就是页面使用率。SQL Server综合每个B树的层次的页空间使用情况,分别生成一个内部碎片百分比。内部碎片可能由下面几种情况导致:

    • 页拆分:页拆分后由于行移动,导致拆分的页面和新页面中出现空白空间。
    • DELETE操作导致页面还剩部分数据。这里的例外是聚集表由于记录被删除时存在虚影,所以不会释放这些删除行的空间,直到后台进程清理后才出现空白空间。
    • 行的大小使得页面填充不完整。例如,聚集索引叶级页中一个宽5000字节的行存放时一页只能放一行,每页都会浪费3000字节左右的空间。

    在读取需要的数据时,内部碎片可能会使系统读取更多的页面,导致IO更大,并且需要更多的内存来存储这些页面。例如,读取聚集键值1-100的记录,如果不出现页拆分,它们可能存储在同一页上,这时只需从磁盘读取1页即可,如果内部碎片多,可能1-50在一页上,51-100在另一页上,这时就需要从磁盘读两页。

    内部碎片也有好处,比如插入行时由于空闲空间的存在,可能不会出现页拆分现象。因此,经常需要DML操作的时候有一定的内部碎片是允许且有益的;但是对经常需要读取巨量数据进行分析的场景,对查询的性能要求较大,内部碎片越少越好。

    可以通过sys.dm_db_index_physical_stats中的avg_page_space_used_in_percent列检测内部碎片。sys.dm_db_index_physical_stats是一个表值函数,它有5个参数,第一个参数是DatabaseID,第二个参数是ObjectID,第三个参数是IndexID,第四个参数是分区ID号,第五个参数是显示信息的模式。

    SELECT  OBJECT_NAME(object_id) AS name,
    
            index_id,
    
            index_type_desc AS index_type,  --索引类型
    
            index_depth,                    --索引B树的深度
    
            index_level,                    --索引B树的层次位置
    
            record_count AS rec_cnt,        --对应层次的记录数量
    
            page_count AS pg_cnt,           --对应层次使用的页的数量
    
            avg_fragmentation_in_percent AS frag_precent,   --外部碎片百分比
    
            avg_page_space_used_in_percent AS used_percent  --内部碎片百分比
    
    FROM    sys.dm_db_index_physical_stats(DB_ID('testdb'),OBJECT_ID('dbo.Clu_Test'),NULL,
    
                                           NULL,'DETAILED')
    

     

    对Clu_test表中的索引进行分析,返回结果看下图,从图可知为Clu_test表中所有B树的每个层次都进行了分析,其中最后一列是内部碎片的情况。

    图片 10

    2.外部碎片

    外部碎片一般还称为逻辑碎片或扩展碎片,是页拆分时出现页的逻辑顺序和物理顺序不一致导致的。很多地方说碎片默认的就是外部碎片。

    那什么是页的逻辑顺序什么是页的物理顺序?页的逻辑顺序是指通过双链表形成的顺序,它能体现B树结构中键值的顺序,因此读取和扫描时按照页的逻辑顺序进行;页的物理顺序是指物理页的页码数值顺序。如果完全按序分配区间和页面,则页面之间不仅在逻辑上连续,在物理页码的数值上也是连续的,比如1-->2-->3。如果页面2出现页拆分,逻辑顺序变成1—>2-->10-->3,这样逻辑顺序和物理顺序将不一致。在页读取或扫描时,会在不连续的页面上不断的进行跳跃定位,很可能会让磁盘臂进行来回移动,从而消耗大量时间。例如从2定位到10进行一次页定位动作,再从10定位回3也要一次定位动作,这需要消耗时间;如果是1-->2-->3-->4这样连续的页就可以快速下一页下一页扫描甚至一次性抓取多个邻近的页到内存中(SQL Server允许一次性读取64个连续的页到内存中,更详细的页读取情况可以查看这篇文章

    如果查询请求的记录较少,外部碎片的影响可以忽略,因为读取页时少量的页定位影响不大;但是如果查询要返回大量记录,由于要读取较多页面,大量的外部碎片会导致多次来回页定位,会严重影响查询性能。可以通过sys.dm_db_index_physical_stats中的avg_fragmentation_in_percent列来检测外部碎片。

     

    传统的机械硬盘读取数据需要先计算地址后寻道,寻道时会移动磁盘臂,寻道后盘片旋转使数据所在扇区处于磁头下方,最后磁头读取扇区数据。扇区数据的读取动作非常快,整个过程的大部分时间都消耗在寻址上。在SQL Server存储机制上,读取一个页和读取一个区的时间几乎是相等的,而页定位很可能意味着要消耗大量时间寻址。因此对于有大量定位动作的读取行为,时间主要消耗在定位上。

    固态硬盘只有得到指令后地址的计算时间,几乎没有寻址时间,不存在定位消耗大量时间的问题,因此外部碎片问题也迎刃而解。

    1.   dbcc showcontig:四部分对象名,【索引名】|【索引id】
    2.   dbcc showcontig:当前库对象id,【索引名】|【索引id】    
    3.   sys.dm_db_index_physical_stats:数据库id,对象id,索引id,分区id,扫描模式

    我们接下来的几篇文章会探讨下面的问题:

    3.重组和重建索引

    重组索引可以将索引的叶级进行重新排列并整理。重组索引使用的是原有的叶级页,重组完成后如果有空页则会释放空页。因为索引重组没有涉及创建索引的过程,因此重组语句中不能指定填充因子,只能默认使用创建索引时指定的填充因子进行重组。重组时会根据内部算法(冒泡排序算法)合理的移动行到合理的位置,尽可能的填充页面空间,并使页的逻辑顺序和物理顺序尽量保持一致,这样可以减少内部碎片和外部碎片。

    和重组索引相比,重建索引更彻底。重建索引会为索引B树(不只是叶级)重新分配一套页面,并释放旧页。重建索引实现的是删除旧碎片(其实是释放旧的页),但是并不能保证重建后完全无碎片。

    例如,新分配的页面之间本身就不连续,或者分配页面的时候正好有其它进程(例如多个CPU并行重建索引时)抢占了中间的页面导致两个进程的页面有交错区域。实际上B树结构中拆分出新层次的页(如第一个中间页或者新的根页)时,都会为新层次的页分配一个中间的页码,如某聚集索引重建最初只有一个页码为208的叶级页,出现第二个叶级页的同时会分配一个根页,根页页码为209,第二个叶级页页码为210,这样根页页码就挤在了叶级页的中间,这也是外部碎片只能无限趋于0但不可能完全被删除的原因之一。

        • 五个参数,基本上,【0(特殊的,index可以为0,故该处为-1)】|【null】|【default】 意义是一样的
    • 如何寻找索引碎片

    • ·使用填充因子

    • ·如何使用ReBuild来提高索引的效率

    • ·如何使用ReOrganize来提高索引的效率

    • ·如何找到缺失的索引

    • 如何找到无用的索引

    • ·如何找到高成本维护索引

    • ·如何使用索引视图提升性能

    • ·如何在计算列上面使用索引提升性能

     

     

    基本指标:

     

    1. 扫描密度(%)[最佳计数:实际计数]:这是“最佳计数”与“实际计数”的比率。如果所有内容都是连续的,则该值为 100;如果该值小于 100,则存在一些碎片。“最佳计数”是指在一切都连续链接的情况下,区更改的理想数目。“实际计数”是指区更改的实际次数。
    2. 逻辑扫描碎片(%):扫描索引的叶级页时返回的出错页的百分比。此数与堆无关。对于出错页,分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页。
    3. 区扫描碎片(%):扫描索引的叶级页时出错区所占的百分比。此数与堆无关。对于出错区,包含当前索引页的区在物理上不是包含上一个索引页的区的下一个区。注意: 如果索引跨越多个文件,则此数字无意义。
    4. avg_page_space_used_in_percent:平均page空间使用率。相关的概念:页拆分、页填充率
    5. avg_fragment_size_in_pages:平均多少个page就有一个碎片,该值 越大越好
    6. avg_fragmentation_in_percent:碎片率,不解释。该值越小越好,和avg_fragment_size_in_pages 反比!
    7. page_count:扫描的总page数
    8. record_count:扫描的总记录数。注意:是相对于当前的扫描来说的记录数,不一定是你所认为的 用户表的一行数据
    9. forwarded_record_count:页拆分的记录数目

    碎片类型简述

     

    内部碎片

    扫描方式

    说到碎片,这里的话题其实就很广了。我们这里主要讨论索引的碎片,至于碎片是如何产生的,我们这里暂不做过多的深究,大家可以参看这篇文章:。对于索引而言,碎片分为两种“外部碎片”和“内部碎片”,我这里用两个图简单的介绍一下:

      索引、堆,因其本质为B数结构,B数是分层级的,故可以多种选择来扫描:非页级?or 仅取一代的样本?or 完全的扫描?

    图片 11图片 12

     

     

    函数的执行模式将确定为了获取此函数所使用的统计信息数据而执行的扫描级别。mode 被指定为 LIMITED、SAMPLED 或 DETAILED。该函数遍历分配单元的页链,这些分配单元构成表或索引的指定分区。sys.dm_db_index_physical_stats 只需要一个意向共享 (IS) 表锁,而忽略其运行所处的模式。有关锁定的详细信息,请参阅锁模式。
    
    LIMITED 模式运行最快,扫描的页数最少。对于索引,只扫描 B 树的父级别页(即叶级别以上的页)。对于堆,只检查关联的 PFS 和 IAM 页;不扫描堆的数据页。在 SQL Server 2005 中,在 LIMITED 模式下扫描堆的所有页。
    
    在 LIMITED 模式下,compressed_page_count 为 NULL,这是因为数据库引擎只能扫描 B 树的非叶页和堆的 IAM 和 PFS 页。使用 SAMPLED 模式可以获取 compressed_page_count 的估计值,使用 DETAILED 模式可以获取 compressed_page_count 的实际值。SAMPLED 模式将返回基于索引或堆中所有页的 1% 样本的统计信息。如果索引或堆少于 10,000 页,则使用 DETAILED 模式代替 SAMPLED。
    
    DETAILED 模式将扫描所有页并返回所有统计信息。
    
    从 LIMITED 到 DETAILED 模式,速度将越来越慢,因为在每个模式中执行的任务越来越多。若要快速测量表或索引的大小或碎片级别,请使用 LIMITED 模式。它的速度最快,并且对于索引的 IN_ROW_DATA 分配单元中的每个非叶级别,不返回与其对应的一行。
    

     

     

    大家可以看到:索引结构的页中,有很多的页中都是没有被填充的,这或许是我故意特定的,如在索引重建重组的时候我们可以指定页面的填充因子,但是很多的时候,这确实我们没有觉察到的。因为我们很多时候都以为索引页是被填满的,但是随着数据的增删改的进行,索引页中就发生了上述的内部碎片。

     

    外部碎片

    最佳实践
    请始终确保使用 DB_ID 或 OBJECT_ID 时返回了有效的 ID。例如,在使用 OBJECT_ID 时,请指定三部分的名称,如 OBJECT_ID(N'AdventureWorks2008R2.Person.Address'),或者在 sys.dm_db_index_physical_stats 函数中使用由函数返回的值之前对这些值进行测试。下面的示例 A 和 B 演示了一种指定数据库和对象 ID 的安全方法。
    
    检测碎片
    在对表进而对表中定义的索引进行数据修改(INSERT、UPDATE 和 DELETE 语句)的整个过程中都会出现碎片。由于这些修改通常并不在表和索引的行中平均分布,所以每页的填充度会随时间而改变。对于扫描表的部分或全部索引的查询,这种碎片会导致附加的页读取。从而延缓了数据的并行扫描。
    
    SQL Server 2008 中的碎片计算算法比 SQL Server 2000 中的更精确。因此,碎片值显得更高。例如,在 SQL Server 2000 中,如果一个表的第 11 页和第 13 页在同一区中,而第 12 页不在该区中,该表不被视为含有碎片。但是访问这些页需要两次物理 I/O 操作,因此,在 SQL Server 2008 中,这将算作碎片。
    
    索引或堆的碎片级别显示在 avg_fragmentation_in_percent 列中。对于堆,此值表示堆的区碎片。对于索引,此值表示索引的逻辑碎片。与 DBCC SHOWCONTIG 不同,这两种情况下的碎片计算算法都会考虑跨越多个文件的存储,因而结果是精确的。
    
    逻辑碎片 
    这是索引的叶级页中出错页所占的百分比。对于出错页,分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页。
    
    区碎片 
    这是堆的叶级页中出错区所占的百分比。出错区是指:包含堆的当前页的区不是物理上的包含前一页的区后的下一个区。
    
    为了获得最佳性能,avg_fragmentation_in_percent 的值应尽可能接近零。但是,从 0 到 10% 范围内的值都可以接受。所有减少碎片的方法(例如重新生成、重新组织或重新创建)都可用于降低这些值。有关如何分析索引中碎片程度的详细信息,请参阅重新组织和重新生成索引。
    
    减少索引中的碎片
    当索引分段的方式导致碎片影响查询性能时,有三种方法可减少碎片:
    
    1、删除并重新创建聚集索引。
    重新创建聚集索引将对数据进行重新分布,从而使数据页填满。填充度可以使用 CREATE INDEX 中的 FILLFACTOR 选项进行配置。这种方法的缺点是索引在删除和重新创建周期内为脱机状态,并且操作属原子级。如果中断索引创建,则不能重新创建索引。有关详细信息,请参阅 CREATE INDEX (Transact-SQL)。
    
    2、使用 ALTER INDEX REORGANIZE(代替 DBCC INDEXDEFRAG)按逻辑顺序重新排序索引的叶级页。由于这是联机操作,因此在语句运行时仍可使用索引。中断此操作时不会丢失已经完成的任务。此方法的缺点是在重新组织数据方面不如索引重新生成操作的效果好,而且不更新统计信息。
    
    
    3、使用 ALTER INDEX REBUILD(代替 DBCC DBREINDEX)联机或脱机重新生成索引。有关详细信息,请参阅 ALTER INDEX (Transact-SQL)。
    
    
    不需要仅因为碎片的原因而重新组织或重新生成索引。碎片的主要影响是,在索引扫描过程中会降低页的预读吞吐量。这将导致响应时间变长。如果含有碎片的表或索引中的查询工作负荷不涉及扫描(因为工作负荷主要是单独查找),则删除碎片可能不起作用。有关详细信息,请参阅此 Microsoft 网站。
    注意: 
    如果在收缩操作中对索引进行部分或完全移动,则运行 DBCC SHRINKFILE 或 DBCC SHRINKDATABASE 可能产生碎片。因此,如果必须执行收缩操作,则不应在删除碎片后进行。
    
    
    
    减少堆中的碎片
    若要减少堆的区碎片,请对表创建聚集索引,然后删除该索引。在创建聚集索引时将重新分布数据。同时会考虑数据库中可用空间的分布,从而使其尽可能优化。当删除聚集索引以重新创建堆时,数据不会移动并保持最佳位置。有关如何执行这些操作的信息,请参阅 CREATE INDEX 和 DROP INDEX。
    
    压缩大型对象数据
    默认情况下,ALTER INDEX REORGANIZE 语句将压缩包含大型对象 (LOB) 数据的页。因为不会释放空的 LOB 页,所以在删除大量 LOB 数据或 LOB 列时,压缩此数据可改善磁盘空间使用情况。
    
    重新组织指定的聚集索引将压缩聚集索引中包含的所有 LOB 列。重新组织非聚集索引将压缩作为索引中非键(已包括)列的所有 LOB 列。如果语句中指定 ALL,则将对与指定表或视图关联的所有索引进行重新组织。此外,将压缩与聚集索引、基础表或带有包含列的非聚集索引关联的所有 LOB 列。
    
    评估磁盘空间使用状况
    avg_page_space_used_in_percent 列指示页填充度。为了使磁盘使用状况达到最优,对于没有很多随机插入的索引,此值应接近 100%。但是,对于具有很多随机插入且页很满的索引,其页拆分数将不断增加。这将导致更多的碎片。因此,为了减少页拆分,此值应小于 100%。使用指定的 FILLFACTOR 选项重新生成索引可以改变页填充度,以便符合索引中的查询模式。有关填充因子的详细信息,请参阅填充因子。此外,ALTER INDEX REORGANIZE 还试图通过将页填充到上一次指定的 FILLFACTOR 来压缩索引。这会增加 avg_space_used_in_percent 的值。请注意,ALTER INDEX REORGANIZE 不会降低页填充度。相反,必须执行索引重新生成。
    
    评估索引碎片
    碎片由分配单元中同一文件内的物理连续的叶级页组成。一个索引至少有一个碎片。索引可以包含的最大碎片数等于索引的页级别页数。碎片越大,意味着读取相同页数所需的磁盘 I/O 越少。因此,avg_fragment_size_in_pages 值越大,范围扫描的性能越好。avg_fragment_size_in_pages 和 avg_fragmentation_in_percent 值成反比。因此,重新生成或重新组织索引会减少碎片数量,但同时增大碎片大小。
    

    所谓的外部碎片,其实这就和索引结构的底层的物理存储相关了。我们上面看到的索引结构,其实就是索引的一个逻辑视图,因为实实在在的索引结构中的页的保存在物理存储上面是这样的:如图:

     

    图片 13

     

     

     

     

    也就说,索引的数据会保存在一块存储空间里面,但是,这块存储空间同时也为其他的结构保存数据,如表,其他的索引等。所以,对一个某个索引,如A而言,那么,它所包含的页的存储的地址可能就不是连续的,如上图所示,里面标红的两个存储位置就是其他的对象的。但是,数据库在读取存储系统上面的数据的时候,是每次都会去读取连续的空间,而不是跳跃性的读取,如上面的,要读取A索引的全部数据,那么上面存储空间中两个标红的空间的数据也会被读取,这就是说:读取了我们原本不应该读取的数据。其实这一点,大家完全可以结合我们平时所知道的磁盘的碎片来理解。

     

     

    下面我们就来看看如何来找碎片:

     

    查找索引碎片

     

    其实,在查找过程中,我们主要是依赖sys.dm_db_index_physical_stats这个动态管理函数。

     

    内部碎片

     

    下面,就给出查找内部碎片的查询,其实原理非常的简单,主要就是看页面空间的使用百分比,然后加上一些经验值的过滤添加,如下:

     

    EXEC sp_configure 'show advanced options',1
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    DECLARE @DefaultFillFactor INT 
    DECLARE @Fillfactor TABLE(Name VARCHAR(100),Minimum INT ,Maximum INT,config_value INT ,run_value INT)
    INSERT INTO @Fillfactor EXEC sp_configure 'fill factor (%)' 
    SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 ELSE run_value END FROM @Fillfactor 

    SELECT
    DB_NAME() AS DBname,
    QUOTENAME(s.name) AS CchemaName,
    QUOTENAME(o.name) AS TableName,
    i.name AS IndexName,
    stats.Index_type_desc AS IndexType,
    stats.page_count AS [PageCount],
    stats.partition_number AS PartitionNumber,
    CASE WHEN i.fill_factor>0 THEN i.fill_factor ELSE @DefaultFillFactor END AS [Fill Factor],
    stats.avg_page_space_used_in_percent,
    CASE WHEN stats.index_level =0 THEN 'Leaf Level' ELSE 'Nonleaf Level' END AS IndexLevel
    FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'DETAILED') AS stats,
    sys.objects AS o,
    sys.schemas AS s,
    sys.indexes AS iWHERE 
    o.OBJECT_ID = stats.OBJECT_ID AND s.schema_id = o.schema_id AND i.OBJECT_ID = stats.OBJECT_ID AND i.index_id = stats.index_idAND stats.avg_page_space_used_in_percent<= 85 AND stats.page_count >= 10 AND stats.index_id > 0 ORDER BY stats.avg_page_space_used_in_percent ASC, stats.page_count DESC

     

    我常常在Where中加入很多的过滤添加,因为之前说过,如果单看原始的数据,没有多大的意义。在上述的查询中,我们只对那些有至少10个页以上的、页面空间使用率小于85%的索引感兴趣。我们在上述查询中重点关注的字段就是:avg_page_space_used_in_percent和PageCount。

    一般而言,导致avg_page_space_used_in_percent偏低的原因如下:

    由于页面分割和删除记录:在这种情况下,我们必须重建或重组的索引。如果碎片在非叶级的,重建需要减少碎片。

    由于填充因子设置:内部的碎片,这是因为填充因子设置的填充因子值的索引错误的设定可能会导致内部碎片,我们必须重建索引选择新的填充因子值。

    由于记录大小:一些数据记录可能导致页发生分裂。例如,我们假设一个记录的大小是3000字节,那么一个索引页只能容纳两个记录。第三个记录不能被安装到一个页面,在页面中剩余的可用空间小于3000个字节。在这种情况下,每一页都将有2060个字节的空的空间。要摆脱的碎片的大小的记录,我们可能需要重新设计表或做一个垂直分区的表。

     

     

    外部碎片

     

    EXEC sp_configure 'show advanced options',1
    GO
    RECONFIGURE WITH OVERRIDE
    G
    ODECLARE @DefaultFillFactor INT
    DECLARE @Fillfactor TABLE(Name VARCHAR(100),Minimum INT ,Maximum INT,config_value INT ,run_value INT)
    INSERT INTO @Fillfactor EXEC sp_configure 'fill factor (%)'
    SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 ELSE run_value END FROM @Fillfactor

    SELECT
    DB_NAME() AS DBname,
    QUOTENAME(s.name) AS CchemaName,
    QUOTENAME(o.name) AS TableName,
    i.name AS IndexName,
    stats.Index_type_desc AS IndexType,
    stats.page_count AS [PageCount],
    stats.partition_number AS PartitionNumber,
    CASE WHEN i.fill_factor>0 THEN i.fill_factor ELSE @DefaultFillFactor END AS [Fill Factor],
    stats.avg_fragmentation_in_percent,stats.fragment_count,
    CASE WHEN stats.index_level =0 THEN 'Leaf Level' ELSE 'Nonleaf Level' END AS IndexLevel
    FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'LIMITED') AS stats,
    sys.objects AS o,
    DML和目录内部结构变化,碎片查看与化解方案。sys.schemas AS s,
    sys.indexes AS iWHERE
    o.OBJECT_ID = stats.OBJECT_ID AND s.schema_id = o.schema_id AND i.OBJECT_ID = stats.OBJECT_ID AND i.index_id = stats.index_idAND stats.avg_fragmentation_in_percent>= 20 AND stats.page_count >= 1000ORDER BY stats.avg_fragmentation_in_percent DESC,stats.page_count DESC

     

     

    一般而言,导致avg_fragmentation_in_percent偏高的原因如下:  

    SQL Server存储引擎从混合区为一个表或索引分配页,直到的表或索引的数据大小达到8个页.一旦它们的页面数达到8页之后,存储引擎开始就开始为它们后续的数据存储分配统一的数据块(extent),然后把数据放在数据块的页中。如果数据库中有很多的小的数据表,那么它们的页将会被放在混合块中,加入,有某个数据表的页有7个,那么可能这7个页被分别放在不同的块中,也就说,这些页之间在存储上面可能是完全不连续的,这将会导致很大的碎片。  

    其他常见的原因是由于DML操作的页面拆分,而新分配的页和之前的页不在连续的存储空间中。

     

    对于维护,给出以下建议:

    • 碎片率在20%至40%,重新组织索引碎片

    • 碎片率在40%以上,考虑重建索引

    • 对于那些索引结构中,页数少于1000的,可以暂时不维护

    • 如果有索引结构中页数超过5万,而且碎片率在10%和20%之间,也将被考虑重组。

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:DML和目录内部结构变化,碎片查看与化解方案

    关键词:

上一篇:新葡亰496netSQL语句计算,创立分区表教程

下一篇:没有了