重建非常大的主鍵索引

我有一個託管在 Azure 上的 SQL 數據庫。問題是大小失控了，我可以在主鍵聚集索引中看到高達 99% 的碎片。

我可以使用online=on選項重建所有其他索引，並且不會影響性能。其中一個 PK Clustered 索引的大小大於 200GB，並且為此 aREBUILD...WITH (ONLINE=ON)會導致鎖定。

我們確實有來自所有時區的使用者訪問該站點，所以真的，我無法找到可以離線重建索引的時間。

在站點不停機的情況下重建大型索引的最佳策略是什麼？

我相信重組無濟於事，因為碎片化率為 99%。問題是即使線上，表也會被鎖定。主要問題是索引大於200GB。主鍵是一個整數。

儘管有點晚了，但我會做出回應，希望它能幫助或至少摒棄對這個問題的一些額外想法/評論，因為我認為這是一個好問題。

首先，我不知道您是否這樣做，但請不要假設索引上的高碎片級別總是會導致性能不佳。陳舊的統計資訊（例如sys.dm_db_stats_properties）和每頁的大量空白空間（即sys.dm_db_index_physical_stats dmv 中的 avg_page_space_used_in_percent 列）與單獨的碎片相比在性能問題方面具有更大的相關性。是的，高度碎片化的索引會產生更多的預讀，並且您通常會看到陳舊的統計資訊和每頁更高級別的空白空間以及碎片化，但碎片化與查詢計劃優化沒有直接關係，也與從磁碟載入索引的記憶體量無關實際上會消耗。 查詢計劃受統計數據的影響，並且您的記憶體佔用會因更多的空白而膨脹。例如，一個 99% 碎片化但平均不到 5% 的索引。與由於過時的統計資訊或因太大而無法完全放入記憶體的索引的持續分頁相比，空白空間和最新的統計資訊可能不會導致嚴重的性能問題，因為存在大量每頁存在的空白空間。

如果碎片確實是一個問題，您可以通過發表ALTER INDEX ... REORGANIZEDan Guzman 在評論中指出的聲明線上減少它。這不會像REBUILD操作那樣創建精簡的索引，但會減少碎片。這裡的關鍵是辨識數據庫上使用率較低的視窗，然後執行它。這可能是 15 分鐘或幾個小時，顯然越長越好，但這裡的關鍵是此操作不會回滾並保留任何進度，即使您在執行過程中終止它也是如此。

如果在一個消除了碎片的完美世界中，在這個表上使用分區會更有意義嗎？ Azure SQL 數據庫確實允許表分區，Microsoft 有一篇很棒的文章概述了Azure SQL 數據庫的一些分區策略。如果您的數據是非易失性的，則對其進行分區可能有助於減少維護需求，如果與表壓縮相結合，您甚至還可以減少整體儲存空間。Alberto Murillo早先的回答暗示使用水平分區基於數據區域，這種方法可能有助於為您創建一些維護視窗，因為您的數據將更具區域性而不是全域性。

由於目前沒有維護視窗，轉換到分區表並不容易，但您可以利用Maria Zakourdaev概述的方法，該方法使用目前表頂部的分區視圖和新的分區表開始分區未來的數據。隨著時間的推移（希望您的舊數據被清除），您最終可以完全過渡到分區表。同樣，我不知道您的數據或應用程序，但也許這種方法是您可以採用的。

首先，重要的是要考慮碎片是否重要。

如果您的查詢只進行單行查找，您可能根本不會注意到碎片。在現代 SAN 上，SAN 級記憶體可以使物理 IO 足夠快，以至於碎片無關緊要。在 SSD 上，掃描碎片索引引起的隨機 IO 模式實際上可能比非碎片數據帶來更好的性能。

很多時候，人們注意到重建索引修復了性能問題。重建索引也會建立新的統計數據。真正的修復可能是新的統計數據，而不是重建索引。UPDATE STATISTICS...WITH FULLSCAN可能是解決相同性能問題的一種更便宜、更快、侵入性更小的方法。

如果您沒有遇到由碎片化引起的問題，那麼您可能會花費大量時間和精力而沒有實際收益。

二、分片有兩種：

1. **物理碎片。**這是大多數人在想到碎片化時會想到的。頁面亂序，需要重新排序。在掃描索引時，這種類型的碎片有時會成為問題。我通常注意到這對物理讀取的性能影響最大。如果您正在查看來自 的結果sys.dm_db_index_physical_stats，則此數字就是avg_fragmentation_in_percent列。
2. **低密度碎片。**這種碎片是由僅部分填充數據的頁面引起的。您的數據密度較低，因為您的數據分佈在不必要的頁面上。因此，讀取數據需要更多的 IO，因為數據分佈在更多的頁面上，而不是必要的。這會影響邏輯和物理讀取。如果您正在查看來自 的結果sys.dm_db_index_physical_stats，則此數字就是avg_page_space_used_in_percent列。此列僅在使用SAMPLEDorDETAILED模式時填充。

那麼你會怎麼做：

物理碎片化：如果您只是為了追求高數字avg_fragmentation_in_percent，請認真考慮您是否在浪費時間。確保您的實際查詢表現不佳，並使用測試環境來確認您正在通過消除碎片來解決問題。

您可以通過執行ALTER INDEX...REORGANIZE. 該REORGANIZE操作是線上的，一次移動一頁以將它們重新組織回物理順序。如果您在REORGANIZE中途終止了一條語句，任何已經執行的工作都會被保留——只有目前正在移動的一個頁面會被回滾。在高度碎片化的大表上執行REORGANIZE操作可能需要更多的總事務日誌空間，並且在完全恢復模式下可能會生成大量事務日誌備份。REORGANIZE高度碎片化的索引也可能比它花費更長的時間REBUILD。

您經常會看到REBUILD對高度碎片化的索引執行 a 的建議，而不是 a REORGANIZE– 這是因為從頭開始重建可能更有效。但是，重組可能是一種“更線上”的操作，有時甚至是高度碎片化的索引也是首選。

低密度碎片無法通過REORGANIZE. 它只能通過做一個來修復ALTER INDEX...REBUILD。通過使用 進行索引ONLINE=ON，您應該能夠最大程度地減少阻塞。但是，REBUILD仍然需要鎖定片刻才能將舊索引換成新索引。在一個非常繁忙的系統上，獲得這種排他鎖有時可能是個問題。您應該能夠通過使用諸如sp_whoisactive 之類的東西來檢查重建期間的阻塞，並查看鎖定和等待的詳細資訊來確認您是否遇到此問題。如果您知道即將到來的低使用率時期，使用該WAIT_AT_LOW_PRIORITY選項可能很有用，並且當活動下降到足以獲得該鎖定時，您的重建可以“潛入”此交換。請注意，長時間執行REBUILD操作也將是一個長期執行的開放式交易。長時間執行的開放事務可能有自己的問題，與事務日誌的使用/重用有關。如果您使用鏡像或可用性組，則還需要考慮在輔助副本上重做事務日誌。

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