一 . 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
二零零六以上)。

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 >10% and val<= 33.33% ————-索引重新组合(碎片收拾)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: 碎片数。

在创建索引时,要求衡量数据更新和数目检索对系统的影响,在实质上付加物情状中,须求安装合适的填写因子,预先留下索引内部碎片;及时收拾索引碎片,消弭索引外界碎片,以使数据库达到最优状态。

二. 灭亡碎片方法

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

图片 2图片 3

目录碎片分为内部碎片(Internal Fragmentation卡塔 尔(阿拉伯语:قطر‎和外界碎片(External
Fragmentation卡塔尔国,内部碎片是指索引页内部的碎片,在索引页内部设有尚未接收的上空,部分空间被闲置,那意味索引页存在空间的荒废,数据实际上占用的半空中多于须要的空中,由此,当存款和储蓄相通的数据集时,若是索引的散装越来越多,索引结构占用的硬盘空间越多;在拍卖数量时,数据库引擎要求读取的索引页更加的多,加载到内存消耗的缓存页(Buffer卡塔尔国越来越多。内部碎片会冒出在目录结构的叶子节点或中等节点,叶子节点中的碎片会导致数据密度减少,而此中节点中的碎片会诱致索引键的密度减弱。

因此施行函数,检查评定索引的零碎:

表面碎片是指积累数据的页或区(Extent卡塔 尔(阿拉伯语:قطر‎的逻辑顺序和物理顺序不相似,逻辑顺序(Logical
Order卡塔尔是由索引键定义的,物理顺序(Physical
Order卡塔尔国是在硬盘文件中,用于存款和储蓄数据的页或区的顺序,相当于索引的叶子节点占用的页或区在硬盘上的大意存款和储蓄的逐大器晚成。假如在逻辑上连续的Page或Extent在物理上也是连连的,那么就不设有外部碎片。最实用的各样是:逻辑顺序上左近的数据页,在情理顺序上也紧邻。

Reorganize and Rebuild
Indexes.aspx)

  • avg_fragmentation_in_percent >5% and <=五分三:
    重新整合索引(ALTE冠道 INDEX REO景逸SUVGANIZE卡塔 尔(英语:State of Qatar);
  • avg_fragmentation_in_percent >二成: 重新创建索引(ALTE福睿斯 INDEX
    REBUILD卡塔 尔(英语:State of Qatar);
  • Limited
    格局是最快的,只扫描最小数据量的Page,Limited格局不会扫描数据页(Data
    Page卡塔 尔(英语:State of Qatar),对于索引,扫描叶子节点的第一手父节点;对于Heap,扫描堆表对应的IAM
    和 PFS系统页。
  • 在萨姆pled情势下,数据库引擎从索引或堆表中收取1%的Page作为样品数量,根据样板数量来打量碎片的水准。
  • Detailed 情势扫描全体的数据页,耗费时间最久,重回的信息最详尽。
select ps.database_id,
    ps.object_id,
    ps.index_id,
    ps.partition_number,
    ps.index_type_desc,
    ps.alloc_unit_type_desc,
    ps.index_depth,
    ps.index_level,
    ps.avg_fragmentation_in_percent,
    ps.fragment_count,
    ps.avg_fragment_size_in_pages,
    ps.page_count,
    ps.avg_page_space_used_in_percent,
    ps.record_count,
    ps.ghost_record_count,
    ps.version_ghost_record_count,
    ps.min_record_size_in_bytes,
    ps.max_record_size_in_bytes,
    ps.avg_record_size_in_bytes,
    ps.forwarded_record_count,
    ps.compressed_page_count
from sys.dm_db_index_physical_stats(database_id,object_id,index_id,partition_number,'detailed') as ps
order by ps.index_level

检查测验索引的零散,须要对索引举行围观,参数mode钦定为了得到碎片数据,数据库引擎必须实施的扫描格局,共有三种形式:LIMITED,
SAMPLED, or DETAILED,暗中同意值是LIMITED。

2,分段和散装

心碎打理有二种艺术:重新组织目录和另行创设索引,重新建立索引是指在二个职业中,删除旧的目录,一视同仁建新的目录,这种方法会回收原有索引的硬盘空间,并分配新的仓库储存空间,以创建索引结构。重新组合索引是指不分配新的蕴藏空间,在原有的长空功底上,重新组织目录结构的叶子节点,使数据页的逻辑顺序和情理顺序保持后生可畏致,并释放索引中多余的空间,那正是说,重新整合索引是为着减小叶子节点的外界碎片。

索引以B-Tree结构存款和储蓄在数据文件中,分为叶子节点和非叶子节点,叶子节点用于存款和储蓄数据,而非叶子节点(中间节点和根节点卡塔尔国用于存储索引键,节点数据依照索引键排序。理论上,风流浪漫旦数据集鲜明下来,索引查找的年华消耗就只跟索引结构的档次有关联,档案的次序越来越多,查找数据所消耗的年月越来越多。碎片会影响索引的档次结构,但是,碎片并不总是破坏者,碎片有支持数据的翻新。

sys.dm_db_index_physical_stats
(Transact-SQL).aspx)

图片 4图片 5

 二,检查实验索引碎片

3,检查实验碎片的台本

风度翩翩,索引碎片

The most efficient order is where the
logical order of the pages and extents(as defined by the index keys,
following the next-page pointers from the page headers) is the same as
the physical order of the pages and extents with the data files. In
other words, the index leaf-lelvel page that has the row with the next
index key is also the next physical contiguous page int the data
file.

数码更新和数目检索是此消彼长的关系,在索引页中留下空闲空间会大增索引的Size,但是,额外占用的硬盘空间要求额外的硬盘IO加载到内存中,那不利于数据的查找,不过,当发生多少更新时,预先留下的空中能够容纳数据行Size的充实,收缩页拆分发生的次数,那便于数据的更新,由此,在一再更新的数据库系统中,为了减小页拆分的次数,必要人工增添索引的内部碎片:

零星(Fragmentation卡塔尔用于描述数据更新对索引结构发生的副功效。页内碎片是指Page
内部设有空闲空间,外界碎片是指Page 或 extent
的情理顺序和所以键定义的逻辑顺序不雷同。