目录碎片的检查测量试验和整治,索引碎片的询

一 . 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 二零零五之上)。

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 >33.33% --------------------------索引重新建立 alter index rebulid with (online=on)
    avg_fragmentation_in_percent:大范围的零散(当碎片大于75%),恐怕供给索引重新营造
  (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: 碎片数。

存储数据是为了查究数据,存款和储蓄结构影响多少检索的性子。对冬季数据开展搜寻,最快的检索算法是哈希查找;对有序数据举行查找,最快的探求算法是平衡树查找。在古板的关系型数据库中,聚集索引和非聚焦索引都以平衡树(B-Tree)类型的囤积结构,用于顺序存款和储蓄数据,便于完毕多少的敏捷找出。除了晋升数据检索的质量之外,索引还是能缩短硬盘IO和内部存款和储蓄器消耗。平时情状下,硬盘IO是搜索质量的瓶颈,由于索引是数据表的列的子集,那象征,索引只存款和储蓄部分列的数目,占用的硬盘空间比任何列少了大多,因而,数据库引擎只供给开销相对相当少的硬盘IO和内存buffer,就能够把索引数据加载到内部存款和储蓄器中。

积存数据是为了查究数据,存款和储蓄结构影响多少检索的属性。对冬日数据举行检索,最快的探索算法是哈希查找;对有序数据进行搜索,最快的检索算法是平衡树查找。在观念的关系型数据库中,集中索引和非集中索引都以平衡树(B-Tree)类型的积攒结构,用于顺序存款和储蓄数据,便于落到实处数据的神速寻觅。除了晋升数据检索的性质之外,索引还能够压缩硬盘IO和内部存款和储蓄器消耗。日常情况下,硬盘IO是研究品质的瓶颈,由于索引是数据表的列的子集,那象征,索引只存款和储蓄部分列的数据,占用的硬盘空间比全体列少了好多,因此,数据库引擎只须求开支相对比较少的硬盘IO和内存buffer,就会把索引数据加载到内部存款和储蓄器中。

目录碎片:

二. 化解碎片方法

-------------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 )

索引以B-Tree结构存款和储蓄在数据文件中,分为叶子节点和非叶子节点,叶子节点用于存款和储蓄数据,而非叶子节点(中间节点和根节点)用于存款和储蓄索引键,节点数据遵照索引键排序。理论上,一旦数据集显明下来,索引查找的岁月费用就只跟索引结构的档期的顺序有提到,档次越多,查找数据所开销的时间越多。碎片会影响索引的档期的顺序结构,可是,碎片并不总是破坏者,碎片有助于数据的更新。

索引以B-Tree结构存款和储蓄在数据文件中,分为叶子节点和非叶子节点,叶子节点用于存款和储蓄数据,而非叶子节点(中间节点和根节点)用于存款和储蓄索引键,节点数据依据索引键排序。理论上,一旦数据集显明下来,索引查找的日子成本就只跟索引结构的层系有涉及,等级次序更加的多,查找数据所开销的时光更多。碎片会影响索引的档次结构,不过,碎片并不总是破坏者,碎片有助于数据的更新。

  • 中间碎片(或说叶级填充率):反映数据叶级的长空占用率或空闲率
  • 外界碎片:由于sqlserver以三番五次的8个page作为二个数量库块(区)extent作为读取单位,故此由于轮廓存款和储蓄上的区和逻辑上不等同(不三番五次)而导致io读取切换

在数码的大要存款和储蓄上,索引和数码存款和储蓄在硬盘上的数据文件中,数据文件以页(Page)为最小单位划分,每二个Page是8KB,物理地方上总是的8个Page叫做四个区(Extent),每三个区是64KB。区是空中分配的着力单位,而页是数码存款和储蓄的着力单位。

在数额的物理存款和储蓄上,索引和多少存储在硬盘上的数据文件中,数据文件以页(Page)为最小单位划分,每一个Page是8KB,物理地方上连年的8个Page叫做贰个区(Extent),每叁个区是64KB。区是空中分配的大旨单位,而页是数额存款和储蓄的主导单位。

 

从物理存储上来看,索引是由一文山会海的分层(Fragment)构成的,每种分段是由三番五次的数据页(Page)构成的。理想状态下,数据存款和储蓄的情理顺序和索引键定义的逻辑顺序保持一致,那便于数据的范围查询,因为固态硬盘不供给活动磁头就足以博得到所需数据。数据的更新(Insert,Update或Delete)一时会更新索引键,组成索引键的字段的Size扩充,以致于原本的Page无法包容该行数据,导致页拆分,致使数据的情理顺序和逻辑顺序不再相称,发生索引外界碎片。由此,预留一些些的页内碎片能够容纳数据行Size的蝇头扩展,减弱页拆分(page split)发生的次数,升高数据更新的性子。平时状态下,多量的目录碎片总是比较重伤的,应该把索引碎片调控在一定百分比以下,微软推荐,四分一。

从物理存款和储蓄上来看,索引是由一文山会海的分层(Fragment)构成的,各样分段是由一而再的数据页(Page)构成的。理想状态下,数据存款和储蓄的大要顺序和索引键定义的逻辑顺序保持一致,这有助于数据的范围查询,因为机械硬盘没有需求活动磁头就足以获得到所需数据。数据的立异(Insert,Update或Delete)一时会更新索引键,组成索引键的字段的Size扩大,以致于原本的Page不能够包容该行数据,导致页拆分,致使数据的情理顺序和逻辑顺序不再相配,发生索引外界碎片。因而,预留少许的页内碎片能够容纳数据行Size的星星增添,缩短页拆分(page split)发生的次数,进步多少更新的个性。常常状态下,大批量的目录碎片总是十三分伤害的,应该把索引碎片调节在肯定百分比以下,微软推举,百分之七十五。

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

数量更新和数量检索是此消彼长的关联,在索引页中留给空闲空间会追加索引的Size,但是,额外占用的硬盘空间需求异常的硬盘IO加载到内部存款和储蓄器中,那不利于数据的物色,不过,当产生多少更新时,预留的半空中能够容纳数据行Size的加码,降低页拆分产生的次数,那有助于数据的换代,由此,在再三更新的数据库系统中,为了降低页拆分的次数,需求人工扩张索引的中间碎片:

数码更新和数目检索是此消彼长的关联,在索引页中留给空闲空间会追加索引的Size,然则,额外占用的硬盘空间要求额外的硬盘IO加载到内部存款和储蓄器中,那不利于数据的搜寻,不过,当发生多少更新时,预留的空中能够容纳数据行Size的加码,裁减页拆分产生的次数,那有助于数据的换代,由此,在一再更新的数据库系统中,为了削减页拆分的次数,需求人工扩张索引的中间碎片:

 

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

询问碎片景况:

在创立索引时,必要权衡数据更新和多少检索对系统的震慑,在实际上产品境遇中,必要设置合适的填充因子,预留索引内部碎片;及时整理索引碎片,解决索引外界碎片,以使数据库达到最优状态。

在开创索引时,须求权衡数据更新和数码检索对系统的震慑,在事实上产品碰着中,须求安装合适的填写因子,预留索引内部碎片;及时整理索引碎片,解决索引外界碎片,以使数据库达到最优状态。

  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】 意义是完全一样的

目录碎片分为内部碎片(Internal Fragmentation)和表面碎片(External Fragmentation),内部碎片是指索引页内部的零散,在索引页内部存在未有动用的长空,部分空间被不了而了,这意味索引页存在空间的浪费,数据实际上据有的空中多于须要的空间,由此,当存款和储蓄同样的数目集时,假使索引的碎片越多,索引结构占用的硬盘空间越来越多;在处理数据时,数据库引擎须求读取的索引页越多,加载到内部存款和储蓄器消耗的缓存页(Buffer)越来越多。内部碎片会合世在目录结构的卡片节点或中等节点,叶子节点中的碎片会形成数据密度裁减,而中等节点中的碎片会导致索引键的密度减弱。

目录碎片分为内部碎片(Internal Fragmentation)和表面碎片(External Fragmentation),内部碎片是指索引页内部的散装,在索引页内部存在未有运用的空中,部分空间被弃置,那意味索引页存在空间的浪费,数据实际上据有的上空多于须求的长空,因而,当存款和储蓄一样的数额集时,假若索引的零碎更加的多,索引结构占用的硬盘空间越来越多;在管理数量时,数据库引擎必要读取的索引页更加多,加载到内存消耗的缓存页(Buffer)更加多。内部碎片会油不过生在目录结构的卡片节点或中等节点,叶子节点中的碎片会招致数据密度裁减,而个中节点中的碎片会导致索引键的密度收缩。

 

外界碎片是指积存数据的页或区(Extent)的逻辑顺序和情理顺序不雷同,逻辑顺序(Logical Order)是由索引键定义的,物理顺序(Physical Order)是在硬盘文件中,用于存款和储蓄数据的页或区的相继,也便是索引的卡片节点占用的页或区在硬盘上的物理存款和储蓄的一一。假如在逻辑上海市总是的Page或Extent在情理上也是连接的,那么就不设有外界碎片。最得力的逐一是:逻辑顺序上相邻的数据页,在轮廓顺序上也紧邻。

外界碎片是指累积数据的页或区(Extent)的逻辑顺序和情理顺序不相同,逻辑顺序(Logical Order)是由索引键定义的,物理顺序(Physical Order)是在硬盘文件中,用于存款和储蓄数据的页或区的一一,也正是索引的卡片节点占用的页或区在硬盘上的物理存款和储蓄的逐个。纵然在逻辑上海市中华全国总工会是的Page或Extent在情理上也是三番一遍的,那么就海市蜃楼外部碎片。最得力的次第是:逻辑顺序上相邻的数据页,在大意顺序上也紧邻。

主导指标:

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.

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.

  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:页拆分的记录数据

 二,质量评定索引碎片

 二,检测索引碎片

 

能够透过松开函数: 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的空间利用率越高。

能够通过松开函数: 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的半空中利用率越高。

环顾方式

1,扫描情势

1,扫描格局

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

检查实验索引的散装,须要对索引进行扫描,参数mode钦赐为了博取碎片数据,数据库引擎必得实践的扫描格局,共有二种形式:LIMITED, SAMPLED, or DETAILED,暗中认可值是LIMITED。

检查测验索引的碎片,须求对索引举行扫描,参数mode内定为了博取碎片数据,数据库引擎必得进行的扫描情势,共有三种格局:LIMITED, SAMPLED, or DETAILED,默许值是LIMITED。

 

  • Limited 格局是最快的,只扫描最小数据量的Page,Limited情势不会扫描数据页(Data Page),对于索引,扫描叶子节点的直接父节点;对于Heap,扫描堆表对应的IAM 和 PFS系统页。
  • 在Sampled形式下,数据库引擎从索引或堆表中抽出1%的Page作为样本数量,依据样本数量来推断碎片的档案的次序。
  • Detailed 方式扫描全数的数据页,耗费时间最久,重返的新闻最详尽。
  • Limited 方式是最快的,只扫描最小数据量的Page,Limited形式不会扫描数据页(Data Page),对于索引,扫描叶子节点的一向父节点;对于Heap,扫描堆表对应的IAM 和 PFS系统页。
  • 在Sampled形式下,数据库引擎从索引或堆表中收取1%的Page作为样本数量,依照样本数量来估算碎片的水平。
  • Detailed 格局扫描全体的数据页,耗费时间最久,重返的音讯最详细。
函数的执行模式将确定为了获取此函数所使用的统计信息数据而执行的扫描级别。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 分配单元中的每个非叶级别,不返回与其对应的一行。

2,分段和心碎

2,分段和散装

 

分段(Fragment),也叫片段,是指在硬盘文件中,数据的物理存款和储蓄的汇总/分散程度。多少个片段是由在情理位置上三番五次的索引页组成的,Fragment的Size 越大,表达页的物理地点越聚集,读取一样数量的Page所需的IO越少,范围读取质量越好。

分段(Fragment),也叫片段,是指在硬盘文件中,数据的物理存款和储蓄的汇聚/分散程度。一个有个别是由在概略地点上连接的索引页组成的,Fragment的Size 越大,表明页的情理地方越聚集,读取一样数量的Page所需的IO越少,范围读取质量越好。

 

心碎(Fragmentation)用于描述数据更新对索引结构爆发的副功效。页内碎片是指Page 内部存在空闲空间,外界碎片是指Page 或 extent 的物理顺序和所以键定义的逻辑顺序不相同样。

零星(Fragmentation)用于描述数据更新对索引结构发生的副效用。页内碎片是指Page 内部存在空闲空间,外界碎片是指Page 或 extent 的情理顺序和所以键定义的逻辑顺序差别样。

最佳实践
请始终确保使用 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 值成反比。因此,重新生成或重新组织索引会减少碎片数量,但同时增大碎片大小。
  • avg_fragmentation_in_percent:碎片百分比,合理的百分比是在10左右,比例越大,索引碎片越多,读取品质越差;
  • fragment_count:分段的数码,理论上,分段(Fragment)数量越少越好,间接表达索引的概略顺序和逻辑顺序越相称;
  • avg_fragment_size_in_pages:每一个分段平均带有的Page数量,Fragment的Size 越大,读取同样数量的Pages所需的IO越少,读取品质越好;
  • avg_page_space_used_in_percent:Page空间的平分利用率,值越大,页内碎片越小;
  • avg_fragmentation_in_percent:碎片百分比,合理的比例是在10左右,比例越大,索引碎片越多,读取品质越差;
  • fragment_count:分段的数目,理论上,分段(Fragment)数量越少越好,直接表明索引的物理顺序和逻辑顺序越相称;
  • avg_fragment_size_in_pages:每个分段平均带有的Page数量,Fragment的Size 越大,读取同样数量的Pages所需的IO越少,读取质量越好;
  • avg_page_space_used_in_percent:Page空间的平均利用率,值越大,页内碎片越小;

 

3,检验碎片的剧本

3,检验碎片的脚本

 

通过执行函数,检查实验索引的零散:

由此实行函数,检查评定索引的散装:

图片 2图片 3

图片 4图片 5

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
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

View Code

View Code

图片 6

图片 7

字段avg_fragmentation_in_percent 表示索引碎片的密度,能够承受的比重是从0到十分一,遵照碎片的比例,接纳重新组织目录或重新创立索引,以整治碎片。

字段avg_fragmentation_in_percent 代表索引碎片的密度,还可以的比例是从0到百分之十,依据碎片的百分比,选取重复协会目录或再度创建索引,以整治碎片。

重临的字段深入分析:

回来的字段解析:

  • 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内空间的平分利用程度
  • 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内空间的平分利用程度

三,碎片整理

三,碎片整理

零星整理有两种艺术:重新组织目录和另行创建索引,重新建立索引是指在几个业务中,删除旧的目录,一视同仁建新的目录,这种方法会回收原有索引的硬盘空间,并分配新的寄存空间,以成立索引结构。重组索引是指不分红新的囤积空间,在原有的长空中基地础上,重新协会目录结构的卡片节点,使数据页的逻辑顺序和轮廓顺序保持一致,并释放索引中多余的空间,那正是说,重组索引是为着削减叶子节点的外表碎片。

心碎整理有两种办法:重新协会目录和重复创建索引,重新营造索引是指在三个事务中,删除旧的目录,相提并论建新的目录,这种方式会回收原有索引的硬盘空间,并分配新的累积空间,以创立索引结构。重组索引是指不分配新的存放空间,在原来的长空中基地础上,重新组织目录结构的叶子节点,使数据页的逻辑顺序和物理顺序保持一致,并释放索引中剩下的空中,那正是说,重组索引是为了减小叶子节点的表面碎片。

使用函数 sys.dm_db_index_physical_stats 检验碎片的等级次序,字段 avg_fragmentation_in_percent   再次回到的逻辑碎片的比重,一般意况下,微软援用以百分之四十为阈值:

使用函数 sys.dm_db_index_physical_stats 检测碎片的水准,字段 avg_fragmentation_in_percent   重回的逻辑碎片的百分比,一般意况下,微软推荐以五分之三为阈值:

  • avg_fragmentation_in_percent >5% and <=四分之一: 重组索引(ALTEOdyssey INDEX REOQX56GANIZE);
  • avg_fragmentation_in_percent >四分一: 重新构造建设索引(ALTEPAJERO INDEX REBUILD);
  • avg_fragmentation_in_percent >5% and <=20%: 重组索引(ALTEWrangler INDEX REOLX570GANIZE);
  • avg_fragmentation_in_percent >百分之三十三: 重新建设构造索引(ALTE智跑 INDEX REBUILD);

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

以下脚本使用游标(Cusor)各种整理索引碎片,在重新建立索引(Rebuild Index)时,使用的目录选项是:FILLFACTORAV4 = 95, ONLINE = OFF, DATA_COMPRESSION = PAGE

图片 8图片 9

图片 10图片 11

DECLARE @SchemeName NVARCHAR(MAX)=N'';
DECLARE @TableName NVARCHAR(MAX)=N'';
DECLARE @IndexName NVARCHAR(MAX)=N'';
DECLARE @avg_fragmentation_in_percent FLOAT=0;
DECLARE @SQL NVARCHAR(MAX)=N'';

DECLARE cur_index CURSOR
LOCAL
FORWARD_ONLY
FAST_FORWARD
READ_ONLY
FOR
SELECT
    '[' s.name ']' AS SchemeName,
    '[' o.name ']' AS TableName,
    '[' i.name ']' AS IndexName,
    MAX(ps.avg_fragmentation_in_percent) AS avg_fragmentation_in_percent
FROM sys.indexes i
INNER JOIN sys.objects o
    ON i.object_id = o.object_id
INNER JOIN sys.schemas s
    ON o.schema_id = s.schema_id
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'DETAILED') AS ps
    ON ps.object_id = i.object_id
    AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent >= 10
AND i.type IN (1, 2)    --1: CLUSTERED, 2: NONCLUSTERED
AND o.type = N'U'        --U: USER_TABLE
AND ps.index_level = 0    --Index leaf-level 
GROUP BY    s.name,
            o.name,
            i.name
ORDER BY avg_fragmentation_in_percent DESC;

OPEN cur_index;

FETCH NEXT FROM cur_index
INTO @SchemeName, @TableName, @IndexName, @avg_fragmentation_in_percent;

WHILE(@@FETCH_STATUS=0)
BEGIN
    IF (@avg_fragmentation_in_percent>30)
    BEGIN
        SELECT @SQL = N'ALTER INDEX '   @IndexName   N' ON '   @SchemeName   N'.'   @TableName 
                          N' REBUILD PARTITION=ALL WITH (FILLFACTOR = 95, ONLINE = OFF, DATA_COMPRESSION = PAGE );'
    END 
    ELSE --@avg_fragmentation_in_percent between 10 and 30
    BEGIN
        SELECT @SQL = N'ALTER INDEX '   @IndexName   N' ON '   @SchemeName   N'.'   @TableName 
                          N' REORGANIZE PARTITION=ALL;'
    END

    EXEC (@SQL)

    FETCH NEXT FROM cur_index
    INTO @SchemeName, @TableName, @IndexName, @avg_fragmentation_in_percent;
END

CLOSE cur_index;
DEALLOCATE cur_index;
DECLARE @SchemeName NVARCHAR(MAX)=N'';
DECLARE @TableName NVARCHAR(MAX)=N'';
DECLARE @IndexName NVARCHAR(MAX)=N'';
DECLARE @avg_fragmentation_in_percent FLOAT=0;
DECLARE @SQL NVARCHAR(MAX)=N'';

DECLARE cur_index CURSOR
LOCAL
FORWARD_ONLY
FAST_FORWARD
READ_ONLY
FOR
SELECT
    '[' s.name ']' AS SchemeName,
    '[' o.name ']' AS TableName,
    '[' i.name ']' AS IndexName,
    MAX(ps.avg_fragmentation_in_percent) AS avg_fragmentation_in_percent
FROM sys.indexes i
INNER JOIN sys.objects o
    ON i.object_id = o.object_id
INNER JOIN sys.schemas s
    ON o.schema_id = s.schema_id
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'DETAILED') AS ps
    ON ps.object_id = i.object_id
    AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent >= 10
AND i.type IN (1, 2)    --1: CLUSTERED, 2: NONCLUSTERED
AND o.type = N'U'        --U: USER_TABLE
AND ps.index_level = 0    --Index leaf-level 
GROUP BY    s.name,
            o.name,
            i.name
ORDER BY avg_fragmentation_in_percent DESC;

OPEN cur_index;

FETCH NEXT FROM cur_index
INTO @SchemeName, @TableName, @IndexName, @avg_fragmentation_in_percent;

WHILE(@@FETCH_STATUS=0)
BEGIN
    IF (@avg_fragmentation_in_percent>30)
    BEGIN
        SELECT @SQL = N'ALTER INDEX '   @IndexName   N' ON '   @SchemeName   N'.'   @TableName 
                          N' REBUILD PARTITION=ALL WITH (FILLFACTOR = 95, ONLINE = OFF, DATA_COMPRESSION = PAGE );'
    END 
    ELSE --@avg_fragmentation_in_percent between 10 and 30
    BEGIN
        SELECT @SQL = N'ALTER INDEX '   @IndexName   N' ON '   @SchemeName   N'.'   @TableName 
                          N' REORGANIZE PARTITION=ALL;'
    END

    EXEC (@SQL)

    FETCH NEXT FROM cur_index
    INTO @SchemeName, @TableName, @IndexName, @avg_fragmentation_in_percent;
END

CLOSE cur_index;
DEALLOCATE cur_index;

View Code

View Code

这一个阈值,能够依据产品遭受数据更新和搜求的实际上景况,适度调治。

那些阈值,能够依靠产品情况数据更新和查究的实际境况,适度调解。

 

 

参照文档:

参照文书档案:

Reorganize and Rebuild Indexes.aspx)

Reorganize and Rebuild Indexes.aspx)

sys.dm_db_index_physical_stats (Transact-SQL).aspx)

sys.dm_db_index_physical_stats (Transact-SQL).aspx)

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:目录碎片的检查测量试验和整治,索引碎片的询

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。