索引碎片 - 我是否正確解釋了結果？

我不是 DBA，但我負責的數據庫目前有數百個表和約 5TB 的數據。我最近執行了以下查詢，希望確定索引碎片：

Declare @DatabaseId Int = DB_ID('ODS')

SELECT 
   OBJECT_NAME(T.OBJECT_ID) as TableName, 
   T2.Name as IndexName,
   T.index_id as IndexId,
   index_type_desc as IndexType,
   index_level as IndexLevel,
   avg_fragmentation_in_percent as AverageFragmentationPercent,
   avg_page_space_used_in_percent as AveragePageSpaceUsedPercent,
   page_count as PageCount
FROM sys.dm_db_index_physical_stats (@DatabaseId, NULL, NULL, NULL, 'DETAILED') T
   INNER JOIN [sys].[indexes] T2 ON T.index_id = T2.index_id And T.object_id = T2.object_id
ORDER BY avg_fragmentation_in_percent DESC

結果集的前 30-40 行如下所示（導入 Excel 後）：

這讓我非常吃驚。我讀對了嗎，我有所有這些索引，實際上更多，是 100% 分散的？我的查詢正確嗎？

可以清楚地看到，page_count對於您附加的圖中顯示的所有索引，< 1500。在這種情況下，即使索引碎片化為 100%，這也不會導致任何性能問題。

實際上，如果您閱讀BOL 2000 版本，以下是 Microsoft 對碎片的建議

碎片會影響磁碟 I/O。因此，請關注較大的索引，因為它們的頁面不太可能被 SQL Server 記憶體。使用 DBCC SHOWCONTIG 報告的頁數來了解索引的大小（每頁大小為 8 KB）。通常，您不應該關心少於 1,000 頁的索引的碎片級別。在測試中，包含超過 10,000 頁的索引實現了性能提升，其中頁數顯著增加（大於 50,000 頁）的索引的性能提升最大。

以下是 Microsoft 團隊對舊連接項的回复（舊連接項已退役，Bugs 和功能請求沒有被帶走），這是為了理解為什麼即使在重建後碎片也沒有減少。

對於小型表，通常無法檢測到碎片對性能的影響。前 8 頁分配將來自混合擴展區，混合擴展區可能位於數據庫文件中的任何位置。重建索引不會改變這種性質。如果你有一個小表，那些混合頁面在碎片計算過程中會很重；因此，重建索引可能不會減少碎片。（事實上，我可以很容易地建構一個重建後碎片增加的案例。）這些碎片不會對您的查詢性能造成痛苦；所以基本上你可以忽略。

您應該使用以下查詢，這將過濾掉 page_count <1500 的不必要索引。建議僅重建 page_count >1500 的索引

Declare @DatabaseId Int = DB_ID('ODS')

SELECT 
   OBJECT_NAME(T.OBJECT_ID) as TableName, 
   T2.Name as IndexName,
   T.index_id as IndexId,
   index_type_desc as IndexType,
   index_level as IndexLevel,
   avg_fragmentation_in_percent as AverageFragmentationPercent,
   avg_page_space_used_in_percent as AveragePageSpaceUsedPercent,
   page_count as PageCount
FROM sys.dm_db_index_physical_stats (@DatabaseId, NULL, NULL, NULL, 'DETAILED') T
   INNER JOIN [sys].[indexes] T2 ON T.index_id = T2.index_id And T.object_id = T2.object_id
where page_count >1500--this would filter out irrelevant index frag.
ORDER BY avg_fragmentation_in_percent DESC

注意：該1500 figure建議不是微軟推薦的硬性規則，而是被廣泛接受的數字。在某些論壇中，您會看到有人使用 1000 的值。核心點是，如果 index 的 page_count 非常少，您不應該重建或重新組織該索引，因為這樣的索引實際上不會導致任何性能問題。

引用自：https://dba.stackexchange.com/questions/115943