您的位置:澳门新葡8455最新网站 > 数据库管理 > 澳门新葡萄京娱乐场索引碎片的查询,索引阐述

澳门新葡萄京娱乐场索引碎片的查询,索引阐述

发布时间:2019-10-09 09:10编辑:数据库管理浏览(115)

    一.概述

        索引填充因子功效:提供填充因子选项是为了优化索引数据存款和储蓄和品质。 当创制或再一次生成索引时,填充因子的值可明显各个叶级页上要填写数据的空中国百货公司分比,以便在每一页上保存部分盈余存款和储蓄空间作为现在扩大索引的可用空间,比方:钦定填充因子的值为 80 表示各个叶级页准将有 十分二的长空保留为空,以便随着向基础表中添增添少而为扩充索引提供空间。

      填充因子的值是 1 到 100 之间的比重,服务器范围的暗中同意值为 0,那象征将完全填充叶级页。

     1.1 页拆分现象

       根据数据的询问和修改的比例,准确选择填充因子值,可提供足够的空中,以便随着向基础表中添增加少而扩张索引,进而减弱页拆分的恐怕。假诺向已满的索引页加多新行(新行地点依照键排序法规,能够是页中放肆行地点), 数据库引擎将把大概四分之二的行移到新页中,以便为该新行腾出空间。 这种重组称为页拆分。页拆分可为新记录腾出空间,不过举行页拆分或许要求开支自然的时光,此操作会消耗大量财富。 另外,它还只怕产生碎片,进而形成 I/O 操作扩张。 如若经常发生页拆分(恐怕过sys.dm_db_index_physical_stats 来查看页拆分景况),可经过行使新的或现存的填充因子值来再一次生成索引,进而再一次分发数据。

      填充钱设置过低: 优点是 插入或涂改时减弱页的拆分次数。弱点是 会使索引须求越来越多的囤积空间,而且会下滑读取品质。

      填充钱设置过高: 优点是 要是每三个索引页数据都全体填满,此时select功用最高。劣势是 插入或改变时要求活动前面全数页,功能低。

    目录碎片:

    二. 碎片与填充因子案例

       上边分析在生产意况下,对长日子贰个表的ix_1索引举办深入分析。

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

      澳门新葡萄京娱乐场 1

        通过上海图书馆能够通晓到平均页密度是29.74%,相当于当中碎片太多,现四个页的数量存款和储蓄量才是平常八个页的存款和储蓄量。扫描的页数是703页,涉及到了1玖拾叁个区。上边重新维护索引

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

      澳门新葡萄京娱乐场 2

         通过上图能够见到,扫描页数唯有了248页(原本是703页) 用了36区(原本是1九十五个区),现等于一页的莫过于数据是后面三页的总数, 查询将会优惠扣了大批量的I/O扫描。

      要是再三的增加和删除改,最棒设置填充因子,暗中同意是0,相当于百分百, 借使有新索引键排序后,挤入到三个已填满8060字节的页中时,就能发生页拆分,产生碎片,这里我动用图形分界面来安装填充因子为85%(最佳通过t-sql来设置,做运行自动保养),再重新创建下索引使设置生效。

      澳门新葡萄京娱乐场 3

      下图能够看见平均页密度是85%,填充因子设置生效。能够在经过sys.dm_db_index_physical_stats重新查看该索引页使用数据。

    澳门新葡萄京娱乐场 4

    • 里头碎片(或说叶级填充率):反映数据叶级的半空中占用率或空闲率
    • 澳门新葡萄京娱乐场,外界碎片:由于sqlserver以三番五次的8个page作为一个多少库块(区)extent作为读取单位,故此由于大要存款和储蓄上的区和逻辑上分裂(不一连)而导致io读取切换

     

    • 逻辑碎片:那是索引的叶级页中出错页所占的比重。对于出错页,分配给索引的下三个物理页不是由近些日子叶级页中的“下一页”指针所针对的页
    • 区碎片:那是堆的叶级页中出错区所占的比例。出错区是指:包括堆的当前页的区不是情理上的包涵前一页的区后的下二个区。(微软真不会分解概念:(

     

    查询碎片情形:

    1.   dbcc showcontig:四局部对象名,【索引名】|【索引id】
    2.   dbcc showcontig:当前库对象id,【索引名】|【索引id】    
    3.   sys.dm_db_index_physical_stats:数据库id,对象id,索引id,分区id,扫描情势
        • 两个参数,基本上,【0(特殊的,index可感觉0,故该处为-1)】|【null】|【default】 意义是一样的

     

    骨干指标:

    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 完全的扫视?

     

    函数的执行模式将确定为了获取此函数所使用的统计信息数据而执行的扫描级别。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 值成反比。因此,重新生成或重新组织索引会减少碎片数量,但同时增大碎片大小。
    

     

     

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:澳门新葡萄京娱乐场索引碎片的查询,索引阐述

    关键词:

上一篇:MySql联接算法,查询优化之

下一篇:没有了