菜单

sql server 索引演说类别五 索引参数与心碎

2019年4月8日 - sqlite

一 . dm_db_index_physical_stats 首要字段表达

  一.1 内部碎片:是avg_page_space_used_in_percent字段。是指页的填充度,为了使磁盘使用情况达到最优,对于未有过多随便插入的目录,此值应接近
100%。 但是,对于具有许多私自插入且页很满的目录,其页拆分数将不止充实。 那将促成更加多的碎片。 由此,为了减小页拆分,此值应小于
十0%。

  一.2外部碎片:也叫逻辑碎片是avg_fragmentation_in_percent字段。是分页的逻辑顺序和情理顺序不合作或然索引拥有的扩大不接二连三时发出。当对表中定义的目录实行数据修改(INSE翼虎T、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 >百分之十 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(内部碎片):最重大列:页面平均使用率也叫存款和储蓄空间的平分百分比,
值越高(以8/10填充度为参考试场点) 页存储数据就越来越多,内部碎片越少。
  (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 参数

  删除或重建三个点名的目录作为单个事务来处理。该项在重建聚集索引时极度有用,当删除2个聚集索引时,sqlserver会重建每一种非聚集索引以便将书签从聚集索引键改为SportageID。假设再新建也许重建聚集索引,Sql
server会再一回重城建总公司体的非聚集索引,即使再新建或重建的聚集索引键值相同,能够设置Drop_existing=ON。

1.3 IGNORE_DUP_KEY

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

1.4 Statistics_norecompute

  选项决定了是不是须要活动更新索引上的总括,各种索引维护着该索引第三个人字段的数值分布的柱状图,在询问执行布置时,查询优化器利用那个总括音信来判断一个一定索引的实惠。当数码达到二个阀值时,计算值会变。Statistics_norecompute选项允许3个关乎的目录在数量修改时不自动更新总计值。该选用覆盖了auto_update_statistics的on值。

1.5 ONLINE   

  值暗中同意OFF,
索引操作时期,基础表和涉及的目录是不是可用以查询和数量修改操作。
  当班值日为ON时,能够持续对基础表和目录进行查询或更新,但在短期内获取sch_m架构修改锁,必须等待此表上的兼具阻塞事务完结,在操作时期,此锁会阻止全体别的工作。
  当值为OFF时,能够会拿走共享锁,防患更新基础表,但允许读操作

1.6 MAXDOP

  索引操作时期代表max degree of parallelism 实例配置,私下认可值为0,
根据近年来系统办事负荷使用实际多少的总括机。

一.七 包蕴性列(included columns)
  包涵列只在叶级别中出现,不控制索引行的11,它效益是使叶级别包括越来越多消息之所以覆盖索引的调优能力,覆盖索引只现出在非聚集索引中,在叶级别就足以找到满足查询的全体新闻。

1.8 on [primary]

  在开立索引时 create index
最后2个子句允许用户钦命索引被放置在哪个地方。能够钦赐特定的文件组或预订义的分区方案。暗中认可存放与表文件组同样1般都以主文件组中。

一.玖羁绊和目录

    当大家创立主键只怕唯一性约束时,会创建二个唯壹性索引,被创制出来协助自律的目录名称与约束名称相同。
  约束是贰个逻辑概念,而索引是二个物理概念,建立目录实际是开创三个占据存款和储蓄空间并且在数额修改操作中必须得到保险的物理构造。
  创造约束就索引内部结构或优化器的精选来看是从未分别的。

二 索引碎片  

  2.1 SHOWCONTIG 

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

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

图片 2

  (一)Page
Scanned-扫描页数:要是您明白行的切近尺寸和表或索引里的行数,那么你能够估计出索引里的页数。看看扫描页数,假设鲜明比你推断的页数要高,表明存在里面碎片。

  (二)Extents
Scanned-扫描扩张盘区数:用扫描页数除以八,四舍伍入到下两个最高值。该值应该和DBCC
SHOWCONTIG重返的扫描扩充盘区数1致。即使DBCC
SHOWCONTIG再次来到的数高,说明存在外部碎片。碎片的惨重程度注重于刚(Yu-Gang)才显示的值比推测值高多少。 

  (叁)Extent
Switches-扩张盘区开关数:该数应该对等扫描扩大盘区数减1。高了则证实有表面碎片。

  (四)Avg. Pages per
Extent-各类扩张盘区上的平分页数:该数是扫描页数除以扫描扩展盘区数,壹般是8。小于八表达有表面碎片。

  (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)-平均页密度(完整):每页上的平均可用字节数的百分比的相反数。低的比重表达有在那之中碎片。

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

相关文章

发表评论

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

网站地图xml地图