数据库sql server 索引阐述种类六 碎片查看与设计

作者:数据库

一 . 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 2006之上)。

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 >百分之十 and val<= 百分之二十五 -------------索引重新整合(碎片收拾) alter index reorganize )
    val >伍分叁 --------------------------索引重新建立 alter index rebulid with (online=on)
    avg_fragmentation_in_percent:大范围的散装(当碎片大于四分之三),大概必要索引重新建立
  (2) page_count:索引或数据页的总额。
  (3) avg_page_space_used_in_percent(内部碎片):最要害列:页面平均使用率也叫存款和储蓄空间的平均百分比, 值越高(以十分之七填充度为参谋点) 页存款和储蓄数据就更多,内部碎片越少。
  (4) avg_record_size_in_bytes:平均记录大小(字节)。
  (5) index_type_desc列:索引类型-聚焦索引可能非聚焦索引等。
  (6) record_count:总记录数,也等于行数。
  (7) fragment_count: 碎片数。

  • avg_fragmentation_in_percent >5% and <=三分之一: 重新整合索引(ALTE讴歌MDX INDEX REO奔驰M级GANIZE);
  • avg_fragmentation_in_percent >30%: 重新构建索引(ALTESportage 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 )

在创设索引时,要求权衡数据更新和数据检索对系统的震慑,在实际上产品情状中,要求设置合适的填充因子,预先留下索引内部碎片;及时收拾索引碎片,消灭索引外部碎片,以使数据库到达最优状态。

 

数据库 2数据库 3

  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:页拆分的笔录数据
  • FILLFACTOR = fillfactor
  • PAD_INDEX = { ON | OFF }

 

表面碎片是指积累数据的页或区(Extent)的逻辑顺序和物理顺序不均等,逻辑顺序(Logical Order)是由索引键定义的,物理顺序(Physical Order)是在硬盘文件中,用于存款和储蓄数据的页或区的次第,相当于索引的卡牌节点占用的页或区在硬盘上的大要存款和储蓄的相继。要是在逻辑上三番两回的Page或Extent在物理上也是三番四回的,那么就不设有外界碎片。最可行的黄金年代一是:逻辑顺序上周围的数据页,在情理顺序上也紧邻。

      • 七个参数,基本上,【0(特殊的,index可以为0,故该处为-1)】|【null】|【default】 意义是同生机勃勃的

零星收拾有三种办法:重新协会目录和重复创制索引,重新建立索引是指在一个思想政治工作中,删除旧的目录,仁同一视建新的目录,这种方法会回笼原有索引的硬盘空间,并分配新的存款和储蓄空间,以成立索引结构。重新整合索引是指不分配新的蕴藏空间,在原有的长空基础上,重新组织目录结构的叶子节点,使数据页的逻辑顺序和情理顺序保持风流倜傥致,并释放索引中多余的空间,那正是说,重新整合索引是为着减小叶子节点的外界碎片。

  • 逻辑碎片:那是索引的叶级页中出错页所占的比重。对于出错页,分配给索引的下多个物理页不是由目前叶级页中的“下大器晚成页”指针所针对的页
  • 区碎片:那是堆的叶级页中出错区所占的百分比。出错区是指:包蕴堆的当前页的区不是情理上的饱含前生龙活虎页的区后的下叁个区。(微软真不会解释概念:(

数据更新和数量检索是此消彼长的关联,在索引页中留下空闲空间会追加索引的Size,可是,额外占用的硬盘空间必要超硬盘IO加载到内部存款和储蓄器中,那不利于数据的找寻,但是,当产生多少更新时,预先留下的空间可以容纳数据行Size的充实,减少页拆分发生的次数,那有助于数据的翻新,因而,在数次更新的数据库系统中,为了减小页拆分的次数,须求人工扩展索引的个中碎片:

 

Reorganize and Rebuild Indexes.aspx)

环顾形式

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
  • 中间碎片(或说叶级填充率):反映数据叶级的上空占用率或空闲率
  • 表面碎片:由于sqlserver以三番五次的8个page作为一个数据库块(区)extent作为读取单位,故此由于大意存款和储蓄上的区和逻辑上不一致样(不总是)而形成io读取切换

sys.dm_db_index_physical_stats (Transact-SQL).aspx)

 

数据库 4数据库 5

查询碎片情状:

通过施行函数,检查评定索引的零散:

 

数据库 6

最佳实践
请始终确保使用 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 值成反比。因此,重新生成或重新组织索引会减少碎片数量,但同时增大碎片大小。

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.

  索引、堆,因其本质为B数结构,B数是分层级的,故能够三种增选来围观:非页级?or 仅取一代的样板?or 完全的扫视?

本文由ca88发布,转载请注明来源

关键词: ca88网址 ca88亚洲备用网 ca88亚洲平台 SQLServer