菜单

sql server 索引演讲体系陆 碎片查看与化解方案

2019年4月9日 - MySQL

一 . dm_db_index_physical_stats 主要字段表明

  1.1 内部碎片:是avg_page_space_used_in_percent字段。是指页的填充度,为了使磁盘使用情状达到最优,对于从未过多即兴插入的目录,此值应接近
100%。 然则,对于具有众多任意插入且页很满的目录,其页拆分数将持续增多。 那将促成更加多的散装。 因而,为了减小页拆分,此值应低于
拾0%。

  一.2外部碎片:也叫逻辑碎片是avg_fragmentation_in_percent字段。是分页的逻辑顺序和物理顺序不包容或然索引拥有的恢弘不总是时产生。当对表中定义的目录举行多少修改(INSE奥迪Q7T、UPDATE
和 DELETE 语句)的整整进度中都会现出零星。
由于那些改动平常并不在表和目录的行中平均分布,所以每页的填充度会随时间而变更。
对于扫描表的有个别或任何索引的询问,这种碎片会促成额外的页读取。
那会妨碍数据的并行扫描。

  壹.三 使用查看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 >百分之三10 ————————–索引重建 alter index
rebulid with (online=on)
    avg_fragmentation_in_percent:大规模的零散(当碎片大于4/10),大概要求索引重建
  (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 参数

  删除或重建1个点名的目录作为单个事务来处理。该项在重建聚集索引时相当有用,当删除一个聚集索引时,sqlserver会重建每一个非聚集索引以便将书签从聚集索引键改为兰德酷路泽ID。假使再新建也许重建聚集索引,Sql
server会再2遍重城建总公司体的非聚集索引,假如再新建或重建的聚集索引键值相同,能够安装Drop_existing=ON。

1.3 IGNORE_DUP_KEY

  是指要是1个update或然insert语句影响多行数据,但有一行键被发现产生重值时,整个讲话就会回滚,IGNORE_DUP_KEY=on时产生重复键值时不会唤起1切讲话的回滚,重复的行会被扬弃别的的行会被插入或更新。

1.4 Statistics_norecompute

  选项决定了是不是要求活动更新索引上的总括,每种索引维护着该索引第4个人字段的数值分布的柱状图,在查询执行安排时,查询优化器利用那几个总括音讯来判定1个一定索引的灵光。当数码达到多少个阀值时,总结值会变。Statistics_norecompute选项允许三个涉及的目录在数据修改时不自动更新总计值。该选拔覆盖了auto_update_statistics的on值。

1.5 ONLINE   

  值暗中同意OFF,
索引操作时期,基础表和事关的目录是还是不是可用于查询和数量修改操作。
  当班值日为ON时,能够继承对基础表和目录举行查询或更新,但在长期内获取sch_m架构修改锁,必须等待此表上的全体阻塞事务达成,在操作时期,此锁会阻止全部其余业务。
  当值为OFF时,能够会拿走共享锁,防患更新基础表,但允许读操作

1.6 MAXDOP

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

一.7 包涵性列(included columns)
  包涵列只在叶级别中出现,不控制索引行的1一,它效益是使叶级别包蕴愈多新闻之所以覆盖索引的调优能力,覆盖索引只现出在非聚集索引中,在叶级别就足以找到满意查询的全部音信。

1.8 on [primary]

  在开立索引时 create index
最终多个子句允许用户钦定索引被停放在哪里。能够钦命特定的文件组或预订义的分区方案。暗中同意存放与表文件组1样一般都以主文件组中。

壹.九封锁和目录

    当大家创制主键恐怕唯一性约束时,会创制二个唯壹性索引,被创制出来帮助自律的目录名称与约束名称相同。
  约束是八个逻辑概念,而索引是贰个大体概念,建立目录实际是开创五个占用存款和储蓄空间并且在数额修改操作中务必获得维护的大体构造。
  创立约束就索引内部结构或优化器的挑选来看是从未区分的。

2 索引碎片  

  2.1 SHOWCONTIG 

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

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

图片 2

  (一)Page
Scanned-扫描页数:尽管您掌握行的切近尺寸和表或索引里的行数,那么你能够推断出索引里的页数。看看扫描页数,假诺显著比你估量的页数要高,表明存在里面碎片。

  (二)Extents
Scanned-扫描增添盘区数:用扫描页数除以八,4舍伍入到下2个最高值。该值应该和DBCC
SHOWCONTIG重返的扫视扩大盘区数壹致。假设DBCC
SHOWCONTIG重临的数高,表达存在外部碎片。碎片的不得了程度依赖Yu Gang才显示的值比估量值高多少。 

  (3)Extent
Switches-扩大盘区开关数:该数应该等于扫描扩充盘区数减壹。高了则印证有表面碎片。

  (肆)Avg. Pages per
Extent-每种扩张盘区上的平分页数:该数是扫描页数除以扫描扩大盘区数,一般是八。小于八表明有表面碎片。

  (5)Scan Density [Best Count:Actual
Count]-扫描密度[最棒值:实际值]:DBCC
SHOWCONTIG重临最可行的一个百分比。那是扩张盘区的最棒值和实际值的比率。该比例应该尽可能接近十0%。低了则表达有表面碎片。

  (6)Logical Scan
Fragmentation-逻辑扫描碎片:冬季页的比重。该比例应该在0%到10%中间,高了则证实有外部碎片。

  (柒)Extent Scan
Fragmentation-扩张盘区扫描碎片:冬辰扩充盘区在扫描索引叶级页中所占的比重。该比例应该是0%,高了则说明有外部碎片。

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

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

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

相关文章

发表评论

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

网站地图xml地图