【997755.com澳门葡京】SQL数据库碎片检查DBCC,MSSQL数据库表索引碎片整理优化性能

-- 创建聚集索引
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]

997755.com澳门葡京 1在SQLServer数据库,通过DBCC
ShowContig或DBCC
ShowContig(表名)检查索引碎片情状,指引我们对其开展定时重建整理。

DBCC SHOWCONTIG是显得指定的表的数额和目录的零散消息。

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

1.1 Filefactor参数

运转结果如下:

  如上图所示。

Usage:

  使用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%

  解释如下:

dbcc SHOWCONTIG

1.2 Drop_existing 参数

相关解释如下:

  Page
Scanned-扫描页数:假如您了解行的切近尺寸和表或索引里的行数,那么您可以估量出索引里的页数。看看扫描页数,假诺显然比你猜度的页数要高,表达存在内部碎片。

[ (

  删除或重建一个指定的目录作为单个事务来拍卖。该项在重建聚集索引时不胜有用,当删除一个聚集索引时,sqlserver会重建每个非聚集索引以便将书签从聚集索引键改为RID。假若再新建或者重建聚集索引,Sql
server会再几遍重建总体的非聚集索引,倘若再新建或重建的聚集索引键值相同,可以安装Drop_existing=ON。

Page
Scanned-扫描页数:假如你精晓行的近乎尺寸和表或索引里的行数,那么你可以估计出索引里的页数。看看扫描页数,尽管彰着比你算计的页数要高,表达存在内部碎片。
Extents
Scanned-扫描扩充盘区数:用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC
SHOWCONTIG重临的围观扩张盘区数一样。如果DBCC
SHOWCONTIG重回的数高,表明存在外部碎片。碎片的要紧程度倚重于刚才来得的值比估摸值高多少。
Extent
Switches-扩张盘区开关数:该数应该等于扫描扩大盘区数减1。高了则申明有表面碎片。
Avg. Pages per
Extent-每个扩张盘区上的平分页数:该数是扫描页数除以扫描扩张盘区数,一般是8。小于8表明有表面碎片。
Scan Density [Best Count:Actual
Count]-扫描密度[最佳值:实际值]:DBCC
SHOWCONTIG重回最有效的一个百分比。这是增添盘区的最佳值和实际值的比率。该比例应该尽量接近100%。低了则证实有外部碎片。

【997755.com澳门葡京】SQL数据库碎片检查DBCC,MSSQL数据库表索引碎片整理优化性能。  Extents
Scanned-扫描扩展盘区数:用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC
SHOWCONTIG重返的扫描扩充盘区数相同。假使DBCC
SHOWCONTIG再次来到的数高,表达存在外部碎片。碎片的要紧程度依赖于刚才体现的值比估算值高多少。

    { ‘table_name’ | table_id |
‘view_name’ | view_id }

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。高了则表达有表面碎片。

    [ , { ‘index_name’ | index_id }
]

  是指假如一个update或者insert语句影响多行数据,但有一行键被发现暴发重值时,整个讲话就会回滚,IGNORE_DUP_KEY=on时发出重复键值时不会引起一切讲话的回滚,重复的行会被遗弃另外的行会被插入或更新。


  Avg. Pages per
Extent-每个扩展盘区上的平均页数:该数是扫描页数除以扫描扩充盘区数,一般是8。小于8表达有外部碎片。

) ]

1.4 Statistics_norecompute

997755.com澳门葡京 2透过对扫描密度(过低),扫描碎片(过高)的结果分析,判定是否需要索引重建。

  Scan Density [Best Count:Actual
Count]-扫描密度[最佳值:实际值]:DBCC
SHOWCONTIG重返最有效的一个比例。这是扩大盘区的最佳值和实际值的比值。该比例应该尽量靠近100%。低了则表明有外部碎片。

    [ WITH

  选项决定了是否需要活动更新索引上的总结,每个索引维护着该索引第一位字段的数值分布的柱状图,在查询执行计划时,查询优化器利用这一个总括信息来判定一个一定索引的有效。当数码达到一个阀值时,总计值会变。Statistics_norecompute选项允许一个提到的目录在数据修改时不自动更新总结值。该选用覆盖了auto_update_statistics的on值。

处理形式:一是应用DBCC INDEXDEFRAG整理索引碎片,二是利用DBCC
DBREINDEX重建索引。二者各有利弊。

  Logical Scan
Fragmentation-逻辑扫描碎片:无序页的比重。该比例应该在0%到10%里边,高了则表明有外部碎片。

        {

997755.com澳门葡京 ,1.5 ONLINE   

调用微软的原话如下:
DBCC INDEXDEFRAG
命令是一同操作,所以索引只有在该命令正在运作时才可用,而且可以在不丢掉已做到工作的情形下刹车该操作。这种措施的瑕疵是在再一次社团数据方面一贯不聚集索引的除了/重新创制操作可行。
再次创立聚集索引将对数码开展重新社团,其结果是使数码页填满。填满程度可以选择FILLFACTOR
选项举行配置。这种艺术的短处是索引在除去/重新创造周期内为脱机状态,并且操作属原子级。即使中断索引成立,则不会再次创立该索引。也就是说,要想博得好的效应,依然得用重建索引,所以决定重建索引。
DBCC DBREINDEX(表,索引名,填充因子)
首先个参数,能够是表名,也得以是表ID。
其次个参数,如若是”,表示影响该表的享有索引。
其两个参数,填充因子,即索引页的数码填充程度。淌倘若100,表示每一个索引页都全体填满,此时select效能最高,但今后要插入索引时,就得移动后边的拥有页,效能很低。假设是0,表示使用以前的填充因子值。

  Extent Scan
Fragmentation-扩大盘区扫描碎片:无序扩大盘区在扫描索引叶级页中所占的比重。该比例应该是0%,高了则印证有外部碎片。

            [ ALL_INDEXES

  值默认OFF,
索引操作期间,基础表和事关的目录是否可用来查询和数量修改操作。
  当值为ON时,可以延续对基础表和目录举办查询或更新,但在长期内获取sch_m架构修改锁,必须等待此表上的具备阻塞事务完成,在操作期间,此锁会阻止所有任何事情。
  当值为OFF时,可以会拿到共享锁,提防更新基础表,但允许读操作

  Avg. Bytes Free per
Page-每页上的平分可用字节数:所扫描的页上的平分可用字节数。越高说明有内部碎片,可是在你用那些数字操纵是否有中间碎片以前,应该考虑fill
factor(填充因子)。

            | FAST [ , ALL_INDEXES
]

1.6 MAXDOP

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

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

            | TABLERESULTS [ , {
ALL_INDEXES } ] ]

  索引操作期间代表max degree of parallelism 实例配置,默认值为0,
遵照当下系统工作负荷使用实际数目标电脑。

 

            [ , { FAST | ALL_LEVELS }
]

1.7 包含性列(included columns)
  包含列只在叶级别中出现,不控制索引行的逐一,它效率是使叶级别包含更多信息之所以覆盖索引的调优能力,覆盖索引只出现在非聚集索引中,在叶级别就足以找到满足查询的漫天音信。

            [ , NO_INFOMSGS ]

1.8 on [primary]

        }

  在开立索引时 create index
最终一个子句允许用户指定索引被停放在哪个地方。可以指定特定的文件组或预定义的分区方案。默认存放与表文件组一样一般都是主文件组中。

    ]

1.9约束和目录

Result:

    当我们创制主键或者唯一性约束时,会创建一个唯一性索引,被创设出来协助自律的目录名称与约束名称相同。
  约束是一个逻辑概念,而索引是一个大体概念,建立目录实际是创设一个据为己有存储空间并且在数量修改操作中必须取得珍贵的情理结构。
  创造约束就索引内部结构或优化器的挑选来看是没有区此外。

DBCC SHOWCONTIG 正在扫描 ‘TB22’
表…

二 索引碎片  

  2.1 SHOWCONTIG 

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

  例如下边查询一个PUB_StockCollect表下的IX_StockModel索引

997755.com澳门葡京 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(填充因子)。

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

  总结:(1)逻辑扫描碎片:越低越好
(2)平均页密度:80%左右最好,低于%60重建索引,(3)最佳计数与事实上计数相差较大重建索引。

表: ‘TB22’ (1877581727);索引 ID:
1,数据库 ID: 7

已执行 TABLE 级另外扫视。


扫描页数…………………………..: 848

  • 扫描区数…………………………:
    115


区切换次数…………………………: 812


每个区的平分页数……………………: 7.4

  • 扫描密度 [一流计数:实际计数]…….:
    13.04% [106:813]

  • 逻辑扫描碎片 ………………:
    95.52%

  • 区扫描碎片 ………………:
    6.96%


每页的平均可用字节数…………………: 2718.2

  • 平均页密度(满)…………………:
    66.42%

 

目录重建后的结果:

DBCC SHOWCONTIG 正在扫描 ‘TB22’
表…

表: ‘TB22’ (1877581727);索引 ID:
1,数据库 ID: 7

已施行 TABLE 级其余围观。


扫描页数…………………………..: 564

  • 扫描区数…………………………:
    71


区切换次数…………………………: 70


每个区的平分页数……………………: 7.9

  • 围观密度 [至上计数:实际计数]…….:
    100.00% [71:71]

  • 逻辑扫描碎片 ………………:
    0.00%

  • 区扫描碎片 ………………:
    1.41%


每页的平均可用字节数…………………: 143.2

  • 平均页密度(满)…………………:
    98.23%

 

分解如下:

Page
Scanned-扫描页数:即使您了解行的好像尺寸和表或索引里的行数,那么你可以估计出索引里的页数。看看扫描页数,假使显然比你推断的页数要高,表明存在内部碎片。

Extents
Scanned-扫描扩展盘区数:用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC
SHOWCONTIG再次回到的扫视扩大盘区数相同。要是DBCC
SHOWCONTIG再次回到的数高,表达存在外部碎片。碎片的严重程度看重于刚才展现的值比揣度值高多少。

Extent
Switches-扩张盘区开关数:该数应该对等扫描扩大盘区数减1。高了则印证有外部碎片。

Avg. Pages per
Extent-每个增加盘区上的平分页数:该数是扫描页数除以扫描扩张盘区数,一般是8。小于8表明有表面碎片。

Scan Density [Best Count:Actual
Count]-扫描密度[最佳值:实际值]:DBCC
SHOWCONTIG再次来到最实用的一个百分比。这是扩充盘区的最佳值和实际值的比值。该比例应该尽量靠近100%。低了则印证有外部碎片。

Logical Scan
Fragmentation-逻辑扫描碎片:无序页的百分比。该比例应该在0%到10%之内,高了则表达有外部碎片。

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

Avg. Bytes Free per
Page-每页上的平分可用字节数:所扫描的页上的平均可用字节数。越高表达有内部碎片,可是在你用那个数字操纵是否有其中碎片以前,应该考虑fill
factor(填充因子)。

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

 

仍是可以够运用以下语句来查看表的情理音信,但该操作会锁表,谨慎采纳

SELECT * FROM
sys.dm_db_index_physical_stats

(DB_ID(N’DB1′), OBJECT_ID(N’dbo.TB22′),
NULL, NULL , ‘DETAILED’);

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website