sql server 索引演讲连串陆 碎片查看与缓解方案

发布时间:2019-04-16  栏目:NoSQL  评论:0 Comments

一 . dm_db_index_physical_stats 主要字段表明

  1.壹 内部碎片:是avg_page_space_used_in_percent字段。是指页的填充度,为了使磁盘使用境况达到最优,对于从未过多随便插入的目录,此值应接近
十0%。 可是,对于具备诸多私行插入且页很满的目录,其页拆分数将不止加码。 那将促成越来越多的碎片。 由此,为了减小页拆分,此值应小于
100%。

  一.2外部碎片:也叫逻辑碎片是avg_fragmentation_in_percent字段。是分页的逻辑顺序和物理顺序不合作只怕索引具有的恢宏不总是时发出。当对表中定义的目录举行数量修改(INSERT、UPDATE
和 DELETE 语句)的全方位经过中都会现出零星。
由于这么些修改常常并不在表和目录的行中平均分布,所以每页的填充度会随时间而改变。
对于扫描表的片段或任何索引的询问,那种碎片会招致额外的页读取。
那会妨碍数据的并行扫描。

  一.三 使用查看dm_db_index_physical_stats索引碎片 (SQL server
200伍之上)。

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 >1/10 and val<= 百分之三十 ————-索引重组(碎片整理)
alter index reorganize )
    val >3/10 ————————–索引重建 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]

2. 消除碎片方法

-------------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 参数

  删除或重建2个点名的目录作为单个事务来拍卖。该项在重建聚集索引时十二分有用,当删除三个聚集索引时,sqlserver会重建每种非聚集索引以便将书签从聚集索引键改为昂科威ID。假如再新建或许重建聚集索引,Sql
server会再二回重城建总公司体的非聚集索引,借使再新建或重建的聚集索引键值同样,能够设置Drop_existing=ON。

1.3 IGNORE_DUP_KEY

  是指借使2个update恐怕insert语句影响多行数据,但有1行键被察觉产生重值时,整个讲话就会回滚,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.柒 包涵性列(included columns)
  包涵列只在叶等级中冒出,不调节索引行的次第,它功能是使叶等第包罗越来越多消息之所以覆盖索引的调优技艺,覆盖索引只现出在非聚集索引中,在叶等第就能够找到知足查询的万事音信。

1.8 on [primary]

  在创设索引时 create index
最终2个子句允许用户钦点索引被停放在哪儿。能够钦点特定的文件组或预订义的分区方案。暗许存放与表文件组一致1般都以主文件组中。

壹.九封锁和目录

    当大家创造主键也许唯1性约束时,会创建3个唯一性索引,被创造出来扶助自律的目录名称与约束名称1致。
  约束是1个逻辑概念,而索引是二个大要概念,建立目录实际是创立一个据为己有存款和储蓄空间并且在数量修改操作中务必取得珍视的情理结构。
  创制约束就索引内部结构或优化器的选项来看是未曾分其他。

贰 索引碎片  

  2.1 SHOWCONTIG 

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

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

图片 2

  (一)Page
Scanned-扫描页数:借使您通晓行的好像尺寸和表或索引里的行数,那么你能够推测出索引里的页数。看看扫描页数,假设显明比你推断的页数要高,表达存在里面碎片。

  (二)Extents
Scanned-扫描扩展盘区数:用扫描页数除以8,4舍伍入到下3个最高值。该值应该和DBCC
SHOWCONTIG重临的扫视扩张盘区数同样。倘使DBCC
SHOWCONTIG再次回到的数高,表明存在外部碎片。碎片的要紧程度看重Yu Gang才展现的值比推测值高多少。 

  (三)Extent
Switches-扩张盘区按键数:该数应该等于扫描扩张盘区数减一。高了则证实有外部碎片。

  (4)Avg. Pages per
Extent-各个扩充盘区上的平均页数:该数是扫描页数除以扫描扩充盘区数,1般是8。小于八表明有外部碎片。

  (5)Scan Density [Best Count:Actual
Count]-扫描密度[最棒值:实际值]:DBCC
SHOWCONTIG再次回到最实用的叁个百分比。那是扩大盘区的最棒值和实际值的比值。该比例应该尽量靠近100%。低了则印证有表面碎片。

  (陆)Logical Scan
Fragmentation-逻辑扫描碎片:严节页的比重。该比例应该在0%到10%之内,高了则注脚有表面碎片。

  (7)Extent Scan
Fragmentation-扩张盘区扫描碎片:冬辰扩大盘区在扫描索引叶级页中所占的比重。该比例应该是0%,高了则印证有表面碎片。

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

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

  总计:(一)逻辑扫描碎片:越低越好
(2)平均页密度:五分四左右最棒,低于%60重建索引,(三)最好计数与实际计数相差较大重建索引。

留下评论

网站地图xml地图