加入 dm_db_index_physical_stats 非常慢

我在下面的 MS Dynamics AX 2012 數據庫中執行一個小查詢，它執行超過 5 分鐘，我不得不取消它，它顯示PAGEIOLATCH_SH等待類型。數據庫數據文件為 560GB，位於 SQL Server 2012 SP1 上。

SELECT TOP 1 A.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A 
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C 
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D 
ON B.object_id = D.object_id AND A.index_id = D.index_id

我在伺服器中檢查了資源監視器的磁碟活動，並且在查詢執行時讀取（B/秒）從最初的 23,000 飆升到超過 13,000,000，並在查詢被取消後又下降。

有什麼想法可能導致這種情況嗎？

磁碟活動很高，因為它需要將整個數據庫拉入 RAM 進行分析。如果您sys.dm_db_index_physical_stats使用較少的 NULL 呼叫，它將能夠在數據庫的一個子部分上執行您的查詢，然後執行得更快。

可悲的是，您TOP 1並沒有阻止它進行所有計算，因為您正在使用所有這些 NULL 呼叫主函式。

…它執行超過 5 分鐘，我不得不取消它並顯示PAGEIOLATCH_SH等待類型

當PAGEIOLATCH_SHSQL Server 正在等待從持久儲存中獲取不在記憶體中的數據或索引頁時，就會發生等待。

在模式（預設）下執行的sys.dm_db_index_physical_stats動態管理功能LIMITED需要讀取非葉級 b 樹索引的IAM/PFS頁面和尚未在記憶體中的堆的頁面。對於 560GB 的數據庫，這很可能會導致大量的 I/O。

它實際上是一個更長的查詢，我用它來獲取按頁數和碎片級別過濾的 TOP N 碎片索引。

你可能在這裡重新發明輪子。流行的免費索引維護解決方案包括：

• Ola Hallengren 的索引和統計維護腳本；和
• MidnightDBA 團隊的 Minion Reindex

這些不一定會使碎片評估更快，但它們確實提供了高度可配置的預建構解決方案，在全球範圍內廣泛使用。Minion Reindex 特別允許在維護視窗之外收集碎片資訊。

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