sql server 索引演说类别六 碎片查看与减轻方案

发布时间:2019-08-29  栏目:MySQL  评论:0 Comments

一 . 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 >百分之十 and val<= 百分之六十 ————-索引重组(碎片整理)
alter index reorganize )
    val >二成 ————————–索引重新建设构造 alter index
rebulid with (online=on)
    avg_fragmentation_in_percent:大规模的碎片(当碎片大于百分之七十五),可能供给索引重新建立
  (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: 碎片数。

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

二. 化解碎片方法

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

1.1 Filefactor参数

  使用Filefactor可以对索引的各样叶子分页存款和储蓄保留部分空间。对于聚焦索引,叶等级富含了数码,使用Filefactor来调整表的保留空间,通过预留的上空,幸免了新的数码按梯次插入时,需腾出空位而进展分页分隔。
  Filefactor设置生效注意,只有在创设索引时才会依赖现已存在的数目调整留下的空中尺寸,如里必要能够alter
index重新创立索引天公地道置原本钦点的Filefactor值。
  在开立索引时,假若不钦赐Filefactor,就应用默许值0
也等于填充满,可透过sp_configure
来布局全局实例。Filefactor也只就用于叶子级分页上。如若要在在那之中层调整索引分页,能够因此点名pad_index慎选来实现.该选用会打招呼到目录上存有档期的顺序使用同一的Filefactor。Pad_index也独有索引在新建或重新创建时有用。

1.2 Drop_existing 参数

  删除或重新创设三个点名的目录作为单个事务来拍卖。该项在重新创立聚焦索引时这么些有用,当删除二个聚焦索引时,sqlserver会重新建构每一个非聚焦索引以便将书签从凑集索引键改为LacrosseID。假设再新建也许重新建立集中索引,Sql
server会再三次重城建总公司体的非聚集索引,假设再新建或重新建立的集中索引键值一样,能够设置Drop_existing=ON。

1.3 IGNORE_DUP_KEY

  是指倘使贰个update大概insert语句影响多行数据,但有一行键被发觉产生重值时,整个讲话就能够回滚,IGNORE_DUP_KEY=on时发出重复键值时不会孳生上上下下讲话的回滚,重复的行会被放弃另外的行会被插入或更新。

1.4 Statistics_norecompute

  选项决定了是或不是须要活动更新索引上的总计,各样索引维护着该索引首位字段的数值布满的柱状图,在询问实施布署时,查询优化器利用那么些统计信息来剖断贰个特定索引的管用。当数码到达二个阀值时,总计值会变。Statistics_norecompute选项允许一个关乎的目录在多少修改时不自动更新总括值。该选用覆盖了auto_update_statistics的on值。

1.5 ONLINE   

  值暗许OFF,
索引操作期间,基础表和关系的目录是还是不是可用以查询和多少修改操作。
  当班值日为ON时,能够一连对基础表和目录实行询问或更新,但在短期内获得sch_m架构修改锁,必需等待此表上的持有阻塞事务达成,在操作时期,此锁会阻止全体另外职业。
  当班值日为OFF时,能够会赢得分享锁,谨防更新基础表,但允许读操作

1.6 MAXDOP

  索引操作时期代表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索引

图片 2

  (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)最棒计数与事实上计数相差十分的大重新建立索引。

留下评论

网站地图xml地图