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 )