提高 sys.dm_db_index_physical_stats 的性能

在維護工作期間，我試圖獲取碎片索引列表。但查詢速度極慢，需要30 多分鐘才能執行。我認為這是由於對 sys.dm_db_index_physical_stats 進行了遠端掃描。

有什麼方法可以加快以下查詢：

SELECT
   OBJECT_NAME(i.OBJECT_ID) AS TableName,
   i.name AS TableIndexName
FROM
   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat 
   INNER JOIN sys.indexes i 
       ON i.OBJECT_ID = phystat.OBJECT_ID AND i.index_id = phystat.index_id 
WHERE 
   phystat.avg_fragmentation_in_percent > 20 
   AND OBJECT_NAME(i.OBJECT_ID) IS NOT NULL
ORDER BY phystat.avg_fragmentation_in_percent DESC

我不是 DBA，可能在上面的查詢中犯了一個明顯的錯誤，或者可能有一些索引或統計數據會有所幫助？也許它只是數據庫的大小（大約 20Gb，大約 140 個表）。

我問的原因是我們在夜間只有一個非常小的維護視窗，這佔用了大部分時間。

'DETAILED'意味著對索引（或堆）中的每一頁進行**全面掃描。**對每個表和每個二級索引執行此操作，結果意味著您正在執行完整的數據庫掃描，端到端，而不是一個非常有效的掃描（例如，幾乎沒有備份讀取它的速度那麼快）。時間由以下因素驅動：

• 你的數據庫有多大
• 您的 IO 子系統讀取整個數據庫的速度有多快
• 額外的並發負載競爭 IO 吞吐量

基本上，如果你只有一根稻草（你的 IO 吞吐量），喝一桶水需要 30 分鐘（你的數據庫大小）。購買更快的 IO、減少數據大小或使用SAMPLED掃描。

話雖這麼說…… 20Gb 非常小。30 分鐘讀取 20Gb 是很多時間。你的 IO 子系統有那麼慢嗎？您是否部署在 7200 RPM 消費者 1TB 驅動器上？

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