澳门游艺场9159-9159金沙游戏场

存储结构影响数据查找的性能

作者: 澳门游艺场  发布:2019-11-29

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

分段(Fragment),也叫片段,是指在硬盘文件中,数据的物理存储的集中/分散程度。一个片段是由在物理位置上连续的索引页组成的,Fragment的Size 越大,说明页的物理位置越集中,读取相同数量的Page所需的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 )

 二,检测索引碎片

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

三,碎片整理

sys.dm_db_index_physical_stats (Transact-SQL).aspx)

可以通过内置函数: 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的空间利用率越高。

这个阈值,可以根据产品环境数据更新和查找的实际情况,适度调整。

  • FILLFACTOR = fillfactor
  • PAD_INDEX = { ON | OFF }

从物理存储上来看,索引是由一系列的分段(Fragment)构成的,每个分段是由连续的数据页(Page)构成的。理想情况下,数据存储的物理顺序和索引键定义的逻辑顺序保持一致,这有利于数据的范围查询,因为机械硬盘不需要移动磁头就可以获取到所需数据。数据的更新(Insert,Update或Delete)有时会更新索引键,组成索引键的字段的Size增加,以至于原来的Page不能容纳该行数据,导致页拆分,致使数据的物理顺序和逻辑顺序不再匹配,产生索引外部碎片。因此,预留少量的页内碎片能够容纳数据行Size的有限增加,减少页拆分(page split)发生的次数,提高数据更新的性能。通常情况下,大量的索引碎片总是十分有害的,应该把索引碎片控制在一定百分比以下,微软推荐,30%。

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

一,索引碎片

  • avg_fragmentation_in_percent >5% and <=30%: 重组索引(ALTER INDEX REORGANIZE);
  • avg_fragmentation_in_percent >30%: 重建索引(ALTER INDEX REBUILD);
  • Index_level=0,表示是索引结构的深度,0表示叶子级别;
  • avg_fragmentation_in_percent:碎片的百分比,表示物理顺序不连续的pages所占的百分比;如果基础表是BTree, 碎片的计量单位是Page,avg_fragmentation_in_percent和page_count 的乘积就是物理顺序和逻辑顺序不一致的pages的总数量。
  • fragment_count:片段的数量
  • page_count:page 的数量
  • avg_fragment_size_in_pages:每个Index 片段平均使用的Pages,是Page_Count和Fragment_Count的比值。
  • avg_page_space_used_in_percent:每个Page内空间的平均使用程度

View Code

参考文档:

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

图片 2图片 3

Reorganize and Rebuild Indexes.aspx)

图片 4图片 5

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.

View Code

本文由澳门游艺场9159发布于澳门游艺场,转载请注明出处:存储结构影响数据查找的性能

关键词: