檢視每一個索引的片段(Index fragmentation)資訊

請參考以下的範例碼：

-- 檢視指定資料庫內的每一個索引之片段(fragmentation)資訊，如下：
SELECT sch.name N'結構描述', obj.name N'資料表',
inx.name AS N'索引名稱',
index_type_desc N'索引類型',
avg_fragmentation_in_percent '片段(%)',
avg_page_space_used_in_percent N'頁面飽和度(%)',
fragment_count,
avg_fragment_size_in_pages,
page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'SAMPLED') AS phy
INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> 'HEAP'  AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 2

-- Get fragmentation for each index  (SAMPLED mode)
SELECT sch.name [Schema], obj.name [Table], inx.name AS [Index], index_type_desc [Index_Type],
 avg_fragmentation_in_percent [Fragmentation(%)], avg_page_space_used_in_percent [Page fullness(%)],
 fragment_count [Fragment_count], avg_fragment_size_in_pages [Avg_Fragment_Size_in_Pages],
 record_count [Record_Count], max_record_size_in_bytes, min_record_size_in_bytes, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'SAMPLED') AS phy
 INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
 INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
 INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> 'HEAP'  AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 2
GO

由上，可以觀察到索引 CL_SID 在關於片段(fragmentation)資訊：
avg_fragmentation_in_percent '片段(%)' -- 51.0280
avg_page_space_used_in_percent N'頁面飽和度(%)' -- 50.9545
fragment_count -- 274
page_count -- 535

一般而言：
avg_fragmentation_in_percent '片段(%)' -- 理想值為 0
avg_page_space_used_in_percent N'頁面飽和度(%)' -- 理想值為 100

雖然 avg_fragmentation_in_percent 的值愈接近零，其效能愈好。
不過，百分之 0 到 10 之間的值都在接受範圍內。
所有縮減片段的方法 (例如，重建、重新組織或重新建立)，都可以用來縮減這些值。

在了解片段的程度後，請使用以下方式來決定修正片段的最好方法：
(1) avg_fragmentation_in_percent 值 > 5% 和 < = 30%，採取重新組織索引。
語法：ALTER INDEX REORGANIZE ......

(2) avg_fragmentation_in_percent 值 > 30%，採取重建索引。
語法：ALTER INDEX REBUILD WITH (ONLINE = ON) 

基本上，不建議使用上述任何命令來處理片段層級過低 (低於 5%) 的情況，這是因為重組或重建索引的成本遠遠超過移除這麼少量的片段所獲得的好處。

重建索引可於線上或離線執行。但重新組織索引則一律在線上執行。
若要達到與重新組織選項相似的可用性，您應該在線上重建索引(使用 Enterprise 版本)。

在執行重建索引作業後：
索引 CL_SID 在關於片段(fragmentation)資訊，如下：
avg_fragmentation_in_percent '片段(%)' -- 0
avg_page_space_used_in_percent N'頁面飽和度(%)' -- 98.7938
fragment_count -- 2
page_count -- 276




參考文件：
重新組織和重建索引
http://technet.microsoft.com/zh-tw/library/ms189858.aspx