sql server 索引解说种类伍 索引参数与零散

发布时间:2019-04-28  栏目:SQL  评论:0 Comments

一 . dm_db_index_physical_stats 主要字段表达

  一.一 内部碎片:是avg_page_space_used_in_percent字段。是指页的填充度,为了使磁盘使用境况达到最优,对于从未过多随机插入的目录,此值接待近
100%。 可是,对于持有众多自便插入且页很满的目录,其页拆分数将不断充实。 那将招致更加多的零散。 因而,为了收缩页拆分,此值应低于
100%。

  一.2外部碎片:也叫逻辑碎片是avg_fragmentation_in_percent字段。是分页的逻辑顺序和概略顺序不相配或许索引具有的恢弘不一连时产生。当对表中定义的目录进行多少修改(INSERT、UPDATE
和 DELETE 语句)的1体进程中都会现出零星。
由于这个退换经常并不在表和目录的行中平均分布,所以每页的填充度会随时间而改动。
对于扫描表的一些或任何索引的询问,这种碎片会导致额外的页读取。
那会妨碍数据的并行扫描。

  一.叁 使用查看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<= 百分之三10 ————-索引重组(碎片整理)
alter index reorganize )
    val >三成 ————————–索引重建 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选用来落成.该选取会通报到目录上全体等级次序使用同1的Filefactor。Pad_index也只有索引在新建或重建时有用。

1.2 Drop_existing 参数

  删除或重建多少个点名的目录作为单个事务来管理。该项在重建聚焦索引时可怜有用,当删除三个聚焦索引时,sqlserver会重建每种非聚焦索引以便将书签从集中索引键改为KugaID。如若再新建或然重建聚焦索引,Sql
server会再1遍重城建总公司体的非聚焦索引,如若再新建或重建的聚焦索引键值相同,能够安装Drop_existing=ON。

1.3 IGNORE_DUP_KEY

  是指假若一个update只怕insert语句影响多行数据,但有壹行键被察觉爆发重值时,整个讲话就会回滚,IGNORE_DUP_KEY=on时发生重复键值时不会引起上上下下讲话的回滚,重复的行会被扬弃别的的行会被插入或更新。

1.4 Statistics_norecompute

  选项决定了是不是供给活动更新索引上的总括,每一个索引维护着该索引第3个人字段的数值布满的柱状图,在询问施行安排时,查询优化器利用那几个总结新闻来判定3个一定索引的管事。当数码达到五个阀值时,总括值会变。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.8 on [primary]

  在成立索引时 create index
最终二个子句允许用户内定索引被放置在哪儿。能够内定特定的文件组或预约义的分区方案。暗中同意存放与表文件组一样一般都是主文件组中。

壹.玖羁绊和目录

    当大家创制主键只怕唯壹性约束时,会成立一个唯1性索引,被创制出来辅助自律的目录名称与约束名称一样。
  约束是二个逻辑概念,而索引是3个物理概念,建立目录实际是创建叁个占领存款和储蓄空间并且在数码修改操作中必须获得保障的物理构造。
  创立约束就索引内部结构或优化器的选择来看是从未分别的。

贰 索引碎片  

  2.1 SHOWCONTIG 

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

  举个例子上边查询3个PUB_StockCollect表下的IX_StockModel索引

图片 2

  (一)Page
Scanned-扫描页数:固然您驾驭行的好像尺寸和表或索引里的行数,那么你能够推断出索引里的页数。看看扫描页数,假若鲜明比你估算的页数要高,表明存在里面碎片。

  (二)Extents
Scanned-扫描增添盘区数:用扫描页数除以8,4舍5入到下一个最高值。该值应该和DBCC
SHOWCONTIG再次回到的扫视扩充盘区数一致。如若DBCC
SHOWCONTIG再次来到的数高,表达存在外部碎片。碎片的惨重程度重视Yu Gang才呈现的值比预计值高多少。 

  (3)Extent
Switches-扩充盘区按钮数:该数应该对等扫描扩大盘区数减一。高了则印证有表面碎片。

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

  (5)Scan Density [Best Count:Actual
Count]-扫描密度[最好值:实际值]:DBCC
SHOWCONTIG再次来到最得力的3个比例。那是扩展盘区的最好值和实际值的比率。该比例应该尽量接近十0%。低了则表达有外部碎片。

  (陆)Logical Scan
Fragmentation-逻辑扫描碎片:冬辰页的百分比。该比例应该在0%到十%时期,高了则证实有外部碎片。

  (七)Extent Scan
Fragmentation-扩张盘区扫描碎片:冬季增添盘区在扫描索引叶级页中所占的百分比。该比例应该是0%,高了则表明有外部碎片。

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

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

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

留下评论

网站地图xml地图