您的位置:金沙游乐场85155 > 大数据库 > sql server 索引阐述系列六 碎片查看与解决方案

sql server 索引阐述系列六 碎片查看与解决方案

发布时间:2019-10-02 09:54编辑:大数据库浏览(50)

    一 . 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: 碎片数。

    • avg_fragmentation_in_percent >5% and <=30%: 重组索引(ALTER INDEX REORGANIZE);
    • avg_fragmentation_in_percent >30%: 重建索引(ALTER INDEX REBUILD);

    二. 解决碎片方法

    -------------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 )
    
    • FILLFACTOR = fillfactor
    • PAD_INDEX = { ON | OFF }

    存储数据是为了查找数据,存储结构影响数据查找的性能。对无序数据进行查找,最快的查找算法是哈希查找;对有序数据进行查找,最快的查找算法是平衡树查找。在传统的关系型数据库中,聚集索引和非聚集索引都是平衡树(B-Tree)类型的存储结构,用于顺序存储数据,便于实现数据的快速查找。除了提升数据查找的性能之外,索引还能减少硬盘IO和内存消耗。通常情况下,硬盘IO是查找性能的瓶颈,由于索引是数据表的列的子集,这意味着,索引只存储部分列的数据,占用的硬盘空间比全部列少了很多,因此,数据库引擎只需要消耗相对较少的硬盘IO和内存buffer,就能把索引数据加载到内存中。

    3,检测碎片的脚本

    可以通过内置函数: sys.dm_db_index_physical_stats,查看索引的外部碎片,字段 avg_fragmentation_in_percent 用于表示外部碎片的程度,对于索引,以Page为单位统计碎片;对于堆(Heap),以Extent为单位统计碎片,这是因为Heap结构的页(Page)是没有顺序的。在堆(Heap)的 Page Header中,字段 next_page 和 Pre_page pointer是null。字段 avg_page_space_used_in_percent 用于表示内部碎片的程度,百分比越高,说明单个Page的空间利用率越高。

    通过执行函数,检测索引的碎片:

    • Limited 模式是最快的,只扫描最小数据量的Page,Limited模式不会扫描数据页(Data Page),对于索引,扫描叶子节点的直接父节点;对于Heap,扫描堆表对应的IAM 和 PFS系统页。
    • 在Sampled模式下,数据库引擎从索引或堆表中抽取1%的Page作为样本数据,根据样本数据来估计碎片的程度。
    • Detailed 模式扫描所有的数据页,耗时最久,返回的信息最详细。
    select ps.database_id,
        ps.object_id,
        ps.index_id,
        ps.partition_number,
        ps.index_type_desc,
        ps.alloc_unit_type_desc,
        ps.index_depth,
        ps.index_level,
        ps.avg_fragmentation_in_percent,
        ps.fragment_count,
        ps.avg_fragment_size_in_pages,
        ps.page_count,
        ps.avg_page_space_used_in_percent,
        ps.record_count,
        ps.ghost_record_count,
        ps.version_ghost_record_count,
        ps.min_record_size_in_bytes,
        ps.max_record_size_in_bytes,
        ps.avg_record_size_in_bytes,
        ps.forwarded_record_count,
        ps.compressed_page_count
    from sys.dm_db_index_physical_stats(database_id,object_id,index_id,partition_number,'detailed') as ps
    order by ps.index_level
    

    外部碎片是指存储数据的页或区(Extent)的逻辑顺序和物理顺序不一致,逻辑顺序(Logical Order)是由索引键定义的,物理顺序(Physical Order)是在硬盘文件中,用于存储数据的页或区的顺序,也就是索引的叶子节点占用的页或区在硬盘上的物理存储的顺序。如果在逻辑上连续的Page或Extent在物理上也是连续的,那么就不存在外部碎片。最有效的顺序是:逻辑顺序上相邻的数据页,在物理顺序上也相邻。

    碎片(Fragmentation)用于描述数据更新对索引结构产生的副作用。页内碎片是指Page 内部存在空闲空间,外部碎片是指Page 或 extent 的物理顺序和所以键定义的逻辑顺序不一致。

    碎片整理有两种方式:重新组织索引和重新创建索引,重建索引是指在一个事务中,删除旧的索引,并重建新的索引,这种方式会回收原有索引的硬盘空间,并分配新的存储空间,以创建索引结构。重组索引是指不分配新的存储空间,在原有的空间基础上,重新组织索引结构的叶子节点,使数据页的逻辑顺序和物理顺序保持一致,并释放索引中多余的空间,这就是说,重组索引是为了减少叶子节点的外部碎片。

    View Code

    三,碎片整理

    The most efficient order is where the logical order of the pages and extents(as defined by the index keys, following the next-page pointers from the page headers) is the same as the physical order of the pages and extents with the data files. In other words, the index leaf-lelvel page that has the row with the next index key is also the next physical contiguous page int the data file.

    参考文档:

    返回的字段分析:

    图片 2图片 3

    Reorganize and Rebuild Indexes.aspx)

    2,分段和碎片

     二,检测索引碎片

    以下脚本使用游标(Cusor)逐个整理索引碎片,在重建索引(Rebuild Index)时,使用的索引选项是:FILLFACTOR = 95, ONLINE = OFF, DATA_COMPRESSION = PAGE

    本文由金沙游乐场85155发布于大数据库,转载请注明出处:sql server 索引阐述系列六 碎片查看与解决方案

    关键词:

上一篇:mysql复制表以及复制数据库

下一篇:没有了