sql server 索引阐述系列六碎片查看与解决方案-阿里云开发者社区

sql server 索引阐述系列六碎片查看与解决方案

2018-08-09 755

简介： 原文:sql server 索引阐述系列六碎片查看与解决方案一 . dm_db_index_physical_stats 重要字段说明　　1.1 内部碎片：是avg_page_space_used_in_percent字段。

+关注继续查看

原文:sql server 索引阐述系列六碎片查看与解决方案

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