索引参数与碎片,SQL数据库碎片检查DBCC

时间:2019-10-10 18:27来源:江苏十一选五手机版数据库
-- 创建聚集索引create table [dbo].[pub_stocktest] add constraint [pk_pub_stocktest] primary key clustered ([sid] asc)with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, online = off, allow_
-- 创建聚集索引
create table [dbo].[pub_stocktest] add  constraint [pk_pub_stocktest] primary key clustered 
(
[sid] asc
)with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, 
online = off, allow_row_locks = on, allow_page_locks = on) on [primary]

-- 创建非聚集索引
 create nonclustered index [ix_model] on [dbo].[pub_stocktest]
(
    [model] asc
)
include (     [name]) with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, 
online = off, allow_row_locks = on, allow_page_locks = on, FILLFACTOR = 85) on [primary]

图片 1在SQLServer数据库,通过DBCC ShowContig或DBCC ShowContig(表名)检查索引碎片意况,指点大家对其进行定期重新建立整理。

DBCC SHOWCONTIG是显示钦定的表的数据和目录的零散音讯。

1.1 Filefactor参数

运维结果如下:

  如上海体育地方所示。

  使用Filefactor能够对索引的每种叶子分页存储保留部分上空。对于聚焦索引,叶品级包涵了数量,使用Filefactor来调整表的保留空间,通过预先流出的空间,防止了新的多寡按顺序插入时,需腾出空位而张开分页分隔。
  Filefactor设置生效注意,独有在成立索引时才会基于现已存在的多寡调节留下的空中尺寸,如里须要能够alter index重新建立索引同样保养置原本钦点的Filefactor值。
  在开立索引时,要是不钦点Filefactor,就采纳私下认可值0 也正是填充满,可通过sp_configure 来安插全局实例。Filefactor也只就用于叶子级分页上。若是要在中游层调整索引分页,能够经过点名pad_index选择来完结.该选用会文告到目录上有着档期的顺序使用一样的Filefactor。Pad_index也只有索引在新建或重新建立时有用。

DBCC SHOWCONTIG 正在扫描 'tbModule' 表...
表: 'tbModule'(1845581613);索引 ID: 0,数据库 ID: 9
已执行 TABLE 级别的扫描。
- 扫描页数.....................................: 51
- 扫描扩展盘区数...............................: 9
- 扩展盘区开关数...............................: 8
- 每个扩展盘区上的平均页数.....................: 5.7
- 扫描密度[最佳值:实际值]....................: 77.78%[7:9]
- 扩展盘区扫描碎片.............................: 77.78%
- 每页上的平均可用字节数.......................: 351.1
- 平均页密度(完整)...........................: 95.66%

  解释如下:

1.2 Drop_existing 参数

有关表明如下:

  Page Scanned-扫描页数:即便您知道行的邻近尺寸和表或索引里的行数,那么你能够估量出索引里的页数。看看扫描页数,假诺明显比你估量的页数要高,表达存在里面碎片。

  删除或重新建立一个点名的目录作为单个事务来拍卖。该项在重新建立集中索引时不胜有用,当删除贰个聚焦索引时,sqlserver会重新建立各种非集中索引以便将书签从聚焦索引键改为WranglerID。假诺再新建可能重新建立集中索引,Sql server会再次重城建总公司体的非聚焦索引,即使再新建或重新创建的集中索引键值同样,能够设置Drop_existing=ON。

Page Scanned-扫描页数:假设你知道行的临近尺寸和表或索引里的行数,那么你能够揣测出索引里的页数。看看扫描页数,借使鲜明比你猜度的页数要高,表达存在里面碎片。
Extents Scanned-扫描扩张盘区数:用扫描页数除以8,四舍五入到下叁个最高值。该值应该和DBCC SHOWCONTIG再次回到的扫视扩展盘区数如出一辙。假诺DBCC SHOWCONTIG再次来到的数高,表明存在外界碎片。碎片的惨恻程度注重Yu Gang才展示的值比推断值高多少。
Extent Switches-扩张盘区开关数:该数应该对等扫描扩大盘区数减1。高了则表明有外界碎片。
Avg. Pages per Extent-每一个扩充盘区上的平均页数:该数是扫描页数除以扫描扩充盘区数,日常是8。小于8表明有表面碎片。
Scan Density [Best Count:Actual Count]-扫描密度[最棒值:实际值]:DBCC SHOWCONTIG重回最平价的三个比重。那是增加盘区的最好值和实际值的比率。该比例应该尽可能临近100%。低了则表达有外界碎片。

  Extents Scanned-扫描扩展盘区数:用扫描页数除以8,四舍五入到下三个最高值。该值应该和DBCC SHOWCONTIG重返的扫视扩张盘区数千篇一律。假如DBCC SHOWCONTIG重回的数高,表明存在外界碎片。碎片的惨恻程度正视于刚先生才突显的值比估量值高多少。

1.3 IGNORE_DUP_KEY

Logical Scan Fragmentation-逻辑扫描碎片:冬日页的百分比。该比例应该在0%到10%里边,高了则表明有外界碎片。
Extent Scan Fragmentation-扩大盘区扫描碎片:冬辰扩大盘区在扫描索引叶级页中所占的百分比。该比例应该是0%,高了则证实有外部碎片。
Avg. Bytes Free per Page-每页上的平分可用字节数:所扫描的页上的平分可用字节数。越高表明有个中碎片,可是在你用这一个数字操纵是或不是有内部碎片此前,应该思虑fill factor(填充因子)。
Avg. Page Density (full)-平均页密度(完整):每页上的平分可用字节数的百分比的相反数。低的百分比表达有此中碎片。

  Extent Switches-增添盘区开关数:该数应该相等扫描扩张盘区数减1。高了则注脚有表面碎片。

  是指如若三个update或然insert语句影响多行数据,但有一行键被察觉发生重值时,整个讲话就能回滚,IGNORE_DUP_KEY=on时发生重复键值时不会挑起上上下下讲话的回滚,重复的行会被放弃另外的行会被插入或更新。


  Avg. Pages per Extent-各类扩展盘区上的平均页数:该数是扫描页数除以扫描扩大盘区数,日常是8。小于8表明有外界碎片。

1.4 Statistics_norecompute

图片 2通过对扫描密度(过低),扫描碎片(过高)的结果剖判,推断是或不是必要索引重新营造。

  Scan Density [Best Count:Actual Count]-扫描密度[最佳值:实际值]:DBCC SHOWCONTIG再次来到最实用的三个百分比。那是扩展盘区的最好值和实际值的比值。该比例应该尽量临近百分之百。低了则印证有表面碎片。

  选项决定了是或不是必要活动更新索引上的总计,每一个索引维护着该索引首位字段的数值遍布的柱状图,在查询实行布署时,查询优化器利用这一个总结新闻来推断二个特定索引的卓有功用。当数码达到四个阀值时,计算值会变。Statistics_norecompute选项允许一个关乎的目录在数量修改时不自动更新总结值。该选用覆盖了auto_update_statistics的on值。

管理方式:一是使用DBCC INDEXDEFRAG整理索引碎片,二是使用DBCC DBREINDEX重新创建索引。二者各有优缺点。

  Logical Scan Fragmentation-逻辑扫描碎片:冬季页的比重。该比例应该在0%到10%里面,高了则证实有外界碎片。

1.5 ONLINE   

调用微软的原话如下:
DBCC INDEXDEFRAG 命令是一块操作,所以索引独有在该命令正在运营时才可用,况兼能够在不吐弃已变成工作的情景下制动踏板该操作。这种方法的弱项是在重复组织数量方面从未聚焦索引的除了/重新创立操作可行。
再一次创造聚集索引将对数据实行再一次组织,其结果是使数据页填满。填满程度足以应用 FILLFACTO奥迪Q7选项进行配备。这种办法的症结是索引在除去/重新创立周期内为脱机状态,并且操作属原子级。借使中断索引创制,则不会再一次创造该索引。也便是说,要想获取好的意义,仍然得用重新建立索引,所以决定重新构造建设索引。
DBCC DBREINDEX(表,索引名,填充因子)
首先个参数,能够是表名,也能够是表ID。
其次个参数,若是是'',表示影响该表的具有索引。
其多个参数,填充因子,即索引页的多寡填充程度。要是是100,表示每三个索引页都全体填满,此时select功能最高,但之后要插入索引时,就得移动前面包车型客车有着页,成效相当低。如若是0,表示使用以前的填写因子值。

  Extent Scan Fragmentation-扩张盘区扫描碎片:严节扩展盘区在扫描索引叶级页中所占的比重。该比例应该是0%,高了则注脚有表面碎片。

  值暗许OFF, 索引操作时期,基础表和涉及的目录是不是可用于查询和数目修改操作。
  当班值日为ON时,能够持续对基础表和目录实行询问或更新,但在长时间内得到sch_m架构修改锁,必需等待此表上的具有阻塞事务达成,在操作时期,此锁会阻止全数任何业务。
  当班值日为OFF时,可以会得到分享锁,防守更新基础表,但允许读操作

  Avg. Bytes Free per Page-每页上的平分可用字节数:所扫描的页上的平均可用字节数。越高表明有在那之中碎片,不过在您用那个数字操纵是不是有中间碎片此前,应该考虑fill factor(填充因子)。

1.6 MAXDOP

--对表tbModule的所有索引进行重建,填充因子比例为80%
DBCC DBREINDEX(tbModule,'',80)  

  Avg. Page Density (full)-平均页密度(完整):每页上的平均可用字节数的百分比的相反数。低的百分比表达有此中碎片。

  索引操作时期代表max degree of parallelism 实例配置,暗许值为0, 依照当前系统办事负荷使用实际数据的Computer。

 

1.7 富含性列(included columns)
  富含列只在叶等第中冒出,不调节索引行的逐一,它效果与利益是使叶等第包蕴越来越多音讯之所以覆盖索引的调优技能,覆盖索引只现出在非聚焦索引中,在叶等第就能够找到满足查询的成套信息。

1.8 on [primary]

  在开创索引时 create index 最终八个子句允许客商钦命索引被停放在哪个地方。能够内定特定的文件组或预约义的分区方案。暗中同意贮存与表文件组一致平时都以主文件组中。

1.9束缚和目录

    当我们创设主键也许唯一性约束时,会创建贰个独一性索引,被创立出来支持自律的目录名称与约束名称一致。
  约束是贰个逻辑概念,而索引是贰个大意概念,建设构造目录实际是创办多少个据有存款和储蓄空间而且在数据修改操作中必须取得爱护的情理结构。
  创制约束就索引内部结构或优化器的选项来看是一向不区别的。

二 索引碎片  

  2.1 SHOWCONTIG 

--   SQLserver 2000使用SHOWCONTIG查看索引碎片 (已过时)
dbcc SHOWCONTIG (tablename,'indexname') 

  比如上面查询一个PUB_StockCollect表下的IX_StockModel索引

图片 3

  (1)Page Scanned-扫描页数:假设您理解行的类似尺寸和表或索引里的行数,那么你可以估量出索引里的页数。看看扫描页数,借使明显比你预计的页数要高,表明存在里面碎片。

  (2)Extents Scanned-扫描扩大盘区数:用扫描页数除以8,四舍五入到下多个最高值。该值应该和DBCC SHOWCONTIG再次来到的扫描扩大盘区数一致。假若DBCC SHOWCONTIG重临的数高,表达存在外界碎片。碎片的深重程度信任Yu Gang才体现的值比推断值高多少。 

  (3)Extent Switches-扩展盘区开关数:该数应该对等扫描增加盘区数减1。高了则印证有表面碎片。

  (4)Avg. Pages per Extent-每一种扩展盘区上的平分页数:该数是扫描页数除以扫描扩张盘区数,日常是8。小于8说明有表面碎片。

  (5)Scan Density [Best Count:Actual Count]-扫描密度[最好值:实际值]:DBCC SHOWCONTIG再次回到最得力的二个比例。那是增添盘区的最好值和实际值的比率。该比例应该尽大概临近100%。低了则表明有表面碎片。

  (6)Logical Scan Fragmentation-逻辑扫描碎片:严节页的百分比。该比例应该在0%到10%里面,高了则证实有外界碎片。

  (7)Extent Scan Fragmentation-增添盘区扫描碎片:冬日增添盘区在扫描索引叶级页中所占的比重。该比例应该是0%,高了则说明有表面碎片。

  (8)Avg. Bytes Free per Page-每页上的平均可用字节数:所扫描的页上的平分可用字节数。越高表达有中间碎片,但是在您用那些数字垄断(monopoly)是还是不是有此中碎片以前,应该怀想fill factor(填充因子)。

  (9)Avg. Page Density (full)-平均页密度(完整):每页上的平分可用字节数的比重的相反数。低的比例表明有中间碎片。

  计算:(1)逻辑扫描碎片:越低越好 (2)平均页密度:百分之八十左右最佳,低于%60重新构建索引,(3)最好计数与实际计数相差非常大重新创立索引。

编辑:江苏十一选五手机版数据库 本文来源:索引参数与碎片,SQL数据库碎片检查DBCC

关键词: