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

-- 创建聚集索引
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会重新建立每种非集中索引以便将书签从聚焦索引键改为TiggoID。如若再新建或许重新创设聚集索引,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经过对扫描密度(过低卡塔 尔(英语:State of Qatar),扫描碎片(过高卡塔尔的结果剖判,推断是或不是供给索引重新创建。

  Scan Density [Best Count:Actual
Count]-扫描密度[最佳值:实际值]:DBCC
SHOWCONTIG重临最可行的二个比重。那是扩充盘区的最棒值和实际值的比值。该比例应该尽量临近百分百。低了则表达有表面碎片。

  选项决定了是或不是要求活动更新索引上的总结,种种索引维护着该索引第1位字段的数值遍布的柱状图,在询问执行布置时,查询优化器利用那么些总结音信来判断一个一定索引的得力。当数码到达一个阀值时,总计值会变。Statistics_norecompute选项允许一个涉嫌的目录在数据修正时不自动更新总结值。该选拔覆盖了auto_update_statistics的on值。

管理方式:一是行使DBCC INDEXDEFRAG收拾索引碎片,二是采用DBCC
DBREINDEX重新建立索引。二者有利有弊。

  Logical Scan
Fragmentation-逻辑扫描碎片:冬天页的比例。该比例应该在0%到一成中间,高了则证实有表面碎片。

1.5 ONLINE   

调用微软的原话如下:
DBCC INDEXDEFRAG
命令是联合操作,所以索引独有在该命令正在运维时才可用,并且可以在不甩掉已形成专门的工作的图景下脚刹踏板该操作。这种办法的败笔是在再一次组织数据方面并未有聚焦索引的不外乎/重新创造操作可行。
重复创建集中索引将对数码举办再度组织,其结果是使数码页填满。填满程度足以采用FILLFACTO昂科拉选项举行安排。这种办法的宿疾是索引在除去/重新创制周期内为脱机状态,并且操作属原子级。假使中断索引创立,则不会再次成立该索引。也正是说,要想博得好的功力,依然得用重新建设构造索引,所以决定重新建立索引。
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,
依照当前系统办事负荷使用实际数指标计算机。

 

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重返的数高,表达存在外界碎片。碎片的惨恻程度注重于刚同志才显示的值比揣测值高多少。 

  (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-每页上的平均可用字节数:所扫描的页上的平分可用字节数。越高表明有中间碎片,可是在你用那些数字垄断是或不是有中间碎片从前,应该盘算fill
factor(填充因子卡塔 尔(英语:State of Qatar)。

  (9)Avg. Page Density
(full)-平均页密度(完整卡塔尔国:每页上的平分可用字节数的比例的相反数。低的百分比表达有在那之中碎片。

  总计:(1)逻辑扫描碎片:越低越好
(2)平均页密度:十分之八左右最棒,低于%60重新建设构造索引,(3)最棒计数与实际计数相差不小重新创设索引。