sql server 索引闡述系列六 碎片檢視與解決方案

原文: 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 )