索引碎片的查询,索引阐述系列六

日期:2019-10-17编辑作者:数据大全

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

存储数据是为了查找数据,存储结构影响数据查找的性能。对无序数据进行查找,最快的查找算法是哈希查找;对有序数据进行查找,最快的查找算法是平衡树查找。在传统的关系型数据库中,聚集索引和非聚集索引都是平衡树(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结构存储在数据文件中,分为叶子节点和非叶子节点,叶子节点用于存储数据,而非叶子节点(中间节点和根节点)用于存储索引键,节点数据按照索引键排序。理论上,一旦数据集确定下来,索引查找的时间消耗就只跟索引结构的层次有关系,层次越多,查找数据所消耗的时间越多。碎片会影响索引的层次结构,但是,碎片并不总是破坏者,碎片有利于数据的更新。

  • 内部碎片(或说叶级填充率):反映数据叶级的空间占用率或空闲率
  • 外部碎片:由于sqlserver以连续的8个page作为一个数据库块(区)extent作为读取单位,故此由于物理存储上的区和逻辑上不一致(不连续)而造成io读取切换

在数据的物理存储上,索引和数据存储在硬盘上的数据文件中,数据文件以页(Page)为最小单位分割,每一个Page是8KB,物理位置上连续的8个Page叫做一个区(Extent),每一个区是64KB。区是空间分配的基本单位,而页是数据存储的基本单位。

 

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

  • 逻辑碎片:这是索引的叶级页中出错页所占的百分比。对于出错页,分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页
  • 区碎片:这是堆的叶级页中出错区所占的百分比。出错区是指:包含堆的当前页的区不是物理上的包含前一页的区后的下一个区。(微软真不会解释概念:(

数据更新和数据查找是此消彼长的关系,在索引页中预留空闲空间会增加索引的Size,然而,额外占用的硬盘空间需要额外的硬盘IO加载到内存中,这不利于数据的查找,然而,当发生数据更新时,预留的空间能够容纳数据行Size的增加,减少页拆分发生的次数,这有利于数据的更新,因此,在频繁更新的数据库系统中,为了减少页拆分的次数,需要人为增加索引的内部碎片:

 

  • 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)越多。内部碎片会出现在索引结构的叶子节点或中间节点,叶子节点中的碎片会导致数据密度降低,而中间节点中的碎片会导致索引键的密度降低。

 

外部碎片是指存储数据的页或区(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.

  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的空间利用率越高。

扫描方式

1,扫描模式

  索引、堆,因其本质为B数结构,B数是分层级的,故可以多种选择来扫描:非页级?or 仅取一代的样本?or 完全的扫描?

检测索引的碎片,需要对索引进行扫描,参数mode指定为了获取碎片数据,数据库引擎必须执行的扫描模式,共有三种模式:LIMITED, SAMPLED, or DETAILED,默认值是LIMITED。

 

  • 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,分段和碎片

 

分段(Fragment),也叫片段,是指在硬盘文件中,数据的物理存储的集中/分散程度。一个片段是由在物理位置上连续的索引页组成的,Fragment的Size 越大,说明页的物理位置越集中,读取相同数量的Page所需的IO越少,范围读取性能越好。

 

碎片(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空间的平均利用率,值越大,页内碎片越小;

 

3,检测碎片的脚本

 

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

图片 2图片 3

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

图片 4

字段avg_fragmentation_in_percent 表示索引碎片的密度,可以接受的百分比是从0到10%,根据碎片的百分比,选择重新组织索引或重新创建索引,以整理碎片。

返回的字段分析:

  • 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   返回的逻辑碎片的百分比,一般情况下,微软推荐以30%为阈值:

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

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

图片 5图片 6

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

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

 

参考文档:

Reorganize and Rebuild Indexes.aspx)

sys.dm_db_index_physical_stats (Transact-SQL).aspx)

本文由澳门金莎娱乐网站发布于数据大全,转载请注明出处:索引碎片的查询,索引阐述系列六

关键词:

Apache与Tomcat区别联系,WEB开发基本知识

    小编利用asp.net的webform框架举办web应用程序的付出已经大致八年了,在全部开拓生涯中,也选用过一年asp.net的...

详细>>

关键字在值为null的应用举例,mysql查询字段为空

转自:http://www.maomao365.com/?p=6873 在mysql中null包括了not null与ifnull或等等,下面我来给大家介绍在mysql中null的注意事项与...

详细>>

比较两个表不同行,表的记录操作

#mysql中 对于查询结果只显示n条连续行的问题# 在领扣上碰到的一个题目:求满足条件的连续3行结果的显示 X city built...

详细>>

澳门金莎娱乐手机版:开发进阶篇系列

一.  perror 错误代码查看工具           在mysql的使用过程中,可能会出现各种各样的error。这些error有些是由于操...

详细>>