快捷搜索:
来自 网络数据库 2019-09-23 12:17 的文章
当前位置: 67677新澳门手机版 > 网络数据库 > 正文

目录解说种类六

 

一 . 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 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 >10% and val<= 30% -------------索引重组(碎片整理) alter index reorganize )
    val >30% --------------------------索引重建 alter index rebulid with (online=on)
    avg_fragmentation_in_percent:大规模的碎片(当碎片大于40%),可能要求索引重建
  (2) page_count:索引或数据页的总数。
  (3) avg_page_space_used_in_percent(内部碎片):最重要列:页面平均使用率也叫存储空间的平均百分比, 值越高(以80%填充度为参考点) 页存储数据就越多,内部碎片越少。
  (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 )

 

 

上午(20171011)看到qq群里发了一个云栖大会的链接,点进去看了一下sqlserver的专场,刚好是提问环节
有人问了一个问题,原话记不清楚了,
大概的意思(他自己认为)就是说:“SQLServer中重建聚集索引之后会影响非聚集索引的碎片情况,也要顺带重建非聚集索引”
我想大概是他自己认为“重建聚集索引之后会影响到非聚集索引的索引碎片”
提问者跟专家交流这个观点,一开始提问之后还撤了几句堆表RID,聚集表key值啥的。
专家一开始说这两者没有关系(重建聚集索引之后不会影响到非聚集索引的索引碎片),后面被提问之后可能是有点紧张,改口说没注意过这个问题。

 

首先抛出结论:对于聚集索引表,重建聚集索引之后不会影响到非聚集索引的索引碎片,重建聚集索引跟非聚集索引碎片之间的没有关系,完全不搭嘎的。
这些问题,其实尝试自己测试一下不就清楚了么?

 

聚集索引重建之后,对非聚集索引是否有影响

首先,暂且先不扯聚集表堆表啥的了,直接说聚集表,
非聚集索引在叶级直接存储的是聚集索引的key值,在重建聚集索引(或者重组)前后,非聚集索引存储的对应的key值是不变的
重建聚集索引之后,数据的屋里存储位置可能会发生变化,这是会影响到聚集索引的物理存储和碎片情况
但是对于非聚集索引来说,非聚集索引存储的对应的聚集索引的key值是不变的,
那非聚集索引的碎片跟聚集索引的重建与否有个毛的关系。
正如我手机里记录了某个人的电话号码,我只要拨通这个电话就能找到他,我管他是去北京上班还是去南京出差了,跟他在人具体哪里(重建聚集索引,物理位置变化)有毛关系。

这些问题如果不确定的话,测试一下就出来结果了啊,我觉得没有任何疑问的。

 

测试,测试表TestFragment中,Id1字段类型为uniqueidentifier,建立聚集索引,
利用uniqueidentifier的随机性,大批量写入数据之后其碎片变得很大
相反,Id2字段类型INT,以递增的值写入数据,大批量写入数据之后其索引碎片会很小
然后重现Id1上的索引,观察Id2上的索引碎片会不会因为Id1上的索引重建而发生变化

create table TestFragment
(
    Id1 uniqueidentifier,
    Id2 int,
    OtherCol2 varchar(50)
)
go


create unique clustered index IDX_Id1 on TestFragment(Id1);
go

create unique index IDX_Id2 on TestFragment(Id2);
go

begin tran
    declare @i int = 0
    while @i<1000000
    begin
        insert into TestFragment values(NEWID(),@i 1,NEWID());
        set @i = @i 1
    end
commit
go

写入100W数据之后观察两个索引上的碎片,

对于聚集索引(Id1上的索引IDX_Id1):
很明显,聚集索引(因为是uniqueidentifier类型的字段),
其avg_fragmentation_in_percent很高(99.2557236469541),同时avg_page_space_used_in_percent较低(68.9408574252533)
对于非聚集索引(Id1上的索引IDX_Id2):
Id2索引因为是递增的,其avg_fragmentation_in_percent很低(0.528606965174129),也就是说碎片程度很低

图片 2

这里姑且不管聚集索引与非聚集索引的碎片程度,这里重点关注“重建聚集索引之后是否会对非聚集索引碎片情况产生影响”

这里重建聚集索引之后,重新观察索引碎片情况,看截图sys.dm_db_index_physical_stats的查询结果
可以很清楚地发现,重建聚集索引之后,聚集索引本身的碎片发生了很大的变化,碎片基本完全消除(avg_fragmentation_in_percent0.0116986429574169),
但是非聚集索引的碎片情况并没有发生任何一点变化。

图片 3

本文由67677新澳门手机版发布于网络数据库,转载请注明出处:目录解说种类六

关键词: