將索引維護最佳化以改善查詢效能並降低資源耗用量

文章
10/11/2024

適用於：SQL Server Azure SQL Azure SQL 資料庫 Azure SQL 受控執行個體 Analytics Platform System (PDW)

本文能協助您判斷執行索引維護的時機和方法。內文包含索引片段和分頁密度等概念，及其對查詢效能與資源耗用量的影響。文中描述維護索引的方法、重新組織索引與重建索引的過程，並推薦維護索引的實用策略，以便在提升潛在效能的同時，平衡維護所需的資源耗用量。

注意

本文不適用於 Azure Synapse Analytics 專用的 SQL 集區。如需適用於 Azure Synapse Analytics 專用 SQL 集區的索引維護資訊，請參閱在 Azure Synapse Analytics 中為專用 SQL 集區資料表編製索引。

概念：索引片段和分頁密度

索引片段的意義及其對效能的影響：

在 B 型樹狀結構 (資料列存放區) 索引內，當索引的索引內邏輯順序分頁 (根據索引的索引鍵值) 與索引頁中實體順序不相符時，就會存在片段。

注意

文件通常會使用「B 型樹狀結構」一詞來指稱索引。在資料列存放區索引中，資料庫引擎會實作 B+ 樹狀結構。這不適用於資料行存放區索引或經記憶體最佳化的資料表。如需詳細資訊，請參閱《SQL Server 和 Azure SQL 索引架構和設計指南》。
只要對基礎資料進行插入、更新或刪除作業，資料庫引擎就會自動修改索引。例如，在資料表中新增資料列，可能會造成資料列存放區索引中的現有分頁分割，為的是騰出空間來插入新的資料列。過一段時間後，這些修改就可能使索引中的資料變成散佈於資料庫中 (片段)。
針對使用完整或範圍索引掃描來讀取許多分頁的查詢，片段化嚴重的索引會導致查詢效能下降，因為系統需要額外的輸入/輸出，才能讀取資料。為了讀取相同的資料量，查詢需要更大量的小規模輸入/輸出要求，而非少量的大規模輸入/輸出要求。
若儲存體子系統提供比隨機輸入/輸出效能更佳的循序輸入/輸出效能，則索引片段會使效能降低，因為系統需要更多隨機輸入/輸出，才能讀取片斷化的索引。

分頁密度 (又名分頁飽和度) 的意義及其對效能的影響：

資料庫中的每個分頁都包含資料列的一個變數。如果整個分頁都是資料列，則分頁的密度就是 100%。如果分頁為空，則分頁密度為 0%。如果密度為 100% 的分頁分割為兩個分頁來容納新的資料列，則這兩個新分頁的密度大約是 50%。
分頁密度低時，就需要更多分頁，才能儲存相同的資料量。換言之，系統需要更多輸入/輸出才能讀取和寫入這些資料，也需要更多記憶體來快取資料。記憶體有限時，系統會快取查詢需要的較少分頁，導致磁碟輸入/輸出的量提高。因此，分頁密度越低，越會對效能造成負面影響。
當資料庫引擎在索引建立、重建或重組期間將數據列新增至頁面時，如果索引的填滿因數設定為 100 以外的值（或 0，在此內容中相等），則不會完整填滿頁面。這會導致分頁密度較低，進而增加輸入/輸出負擔，並對效能造成負面影響。
分頁密度低可能會增加中繼 B 型樹狀結構等級的數量。這會慢慢增加系統在索引掃描和搜尋時尋找分葉層級分頁時，CPU 和輸入/輸出的費用。
查詢最佳化工具編譯查詢計劃時，會考量讀取查詢所需的資料會產生的輸入/輸出成本。若分頁密度低，則需讀取更多分頁，進而使輸入/輸出費用提高。這會影響查詢計劃的選擇。舉例而言，如果分頁密度因頁面分割逐漸減少，最佳化工具可能會為相同的查詢編譯不同的方案，並提出不同的效能與資源耗用量設定檔。

提示

在許多工作負載中，分頁密度提升都能比減少片段帶進更多正面的效能影響。

為免不必要地降低分頁密度，Microsoft 不建議將填滿因數設定為 100 或 0 以外的值。索引發生大量頁面分割的特定案例者除外，例如索引經過頻繁修改，且前置資料行包含非循序 GUID 值。

測量索引片段和頁面密度

片段和分頁密度都是判斷是否該執行索引維護，以及該使用哪一種維護方法時應該考量的要素。

資料列存放區和資料行存放區索引對片段的定義不同。針對資料列存放區索引，sys.dm_db_index_physical_stats() 有助您在特定索引中、在資料表或索引檢視表上的所有索引、在資料庫中所有索引或在所有資料庫的所有索引中判斷片段和分頁密度。針對資料分割索引，sys.dm_db_index_physical_stats() 能為每個分割提供這項資訊。

由 sys.dm_db_index_physical_stats 所傳回的結果集包含下列資料行：

資料行	描述
`avg_fragmentation_in_percent`	邏輯片段 (索引中失序的分頁)。
`avg_page_space_used_in_percent`	平均分頁密度。

針對資料行存放區索引中壓縮的資料列群組，片段的定義在於已刪除資料行與總資料行的比率，並以百分比表示。 sys.dm_db_column_store_row_group_physical_stats 有助您判斷特定索引、資料表內所有索引，或資料庫內所有索引中，每個資料列群組的資料列總數和已刪除的資料列數。

由 sys.dm_db_column_store_row_group_physical_stats 所傳回的結果集包含下列資料行：

資料行	描述
`total_rows`	實際儲存在資料列群組中的資料列數目。針對已壓縮的資料列群組，這包含標示為已刪除的資料列。
`deleted_rows`	實際儲存在標示為要刪除之已壓縮資料列群組的資料列數目。 0 代表位於差異存放區中的資料列群組。

資料行存放區索引內的壓縮資料列群組片段可透過以下公式進行計算：

100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

提示

針對資料列存放區索引和資料行存放區索引，在刪除或更新大量資料列之後，檢閱索引或堆積片段和分頁密度。針對堆積，如果經常更新，定期檢閱片段以避免轉送記錄激增。如需堆積的詳細資訊，請參閱堆積 (無叢集索引的資料表)。

請參閱範例，了解判斷片段及分頁密度的查詢樣本。

維護索引的方法：重新組織與重建

您可以使用以下方法的其中之一，來減少索引片段並增加分頁密度：

重新組織索引
重建索引

注意

針對資料分割索引，您可在索引的所有分割或在單一資料分割上使用任意下列方法。

重新組織索引

重新組織索引，使用的資源會比重建索引來得少。故此，建議您將之列為偏好的索引維護方法，有特定原因必須使用索引重建時例外。重新組織作業一律必須連線進行。這表示不會保留長期物件層級的鎖定，而且在 ALTER INDEX ... REORGANIZE 作業期間，可以繼續查詢或更新基礎資料表。

針對資料列存放區索引，資料庫引擎只會實際重新排序分葉層級的分頁，使這些分頁符合分葉節點的邏輯順序 (由左至右)，以重新組織資料表與檢視表其叢集與非叢集索引的分葉層級。重新組織也會壓縮索引頁，讓分頁密度與索引的填滿因數相等。若要檢視填滿因數設定，請使用 sys.indexes。如需語法範例，請參閱範例：資料列存放區重新組織。
使用資料行存放區索引時，在一段時間內插入、更新及刪除資料之後，差異存放區最後可能會有多個小型資料列群組。重新組織資料行存放區索引會將差異存放區資料列群組強行移至資料行存放區內的壓縮資料列群組，並將較小的已壓縮資料列群組合併為較大的資料列群組。重新組織作業也會實際移除已從資料行存放區標記為刪除的資料列。重新組織資料行存放區索引可能需要額外的 CPU 資源來壓縮資料。效能可能會在執行作業期間變慢。不過，只要資料壓縮完成，查詢效能就會改善。如需語法範例，請參閱範例：資料行存放區重新組織。

注意

從 SQL Server 2019 (15.x)、Azure SQL Database 和 Azure SQL 受控執行個體開始，背景合併工作將會協助 Tuple Mover，該工作會自動壓縮已存在一段時間的較小已開啟差異資料列群組 (由內部閾值決定)，或合併已刪除大量資料列的已壓縮資料列群組。這可改善一段時間的資料行存放區索引品質。在多數案例中，這麼做會除去發出 ALTER INDEX ... REORGANIZE 命令的需求。

提示

如果您取消重新組織作業，或是作業過程受到中斷，則系統到中斷為止的進度會保存於資料庫中。若要重新組織大型索引，則一直到完成為止，作業過程可多次開始及暫停。

重建索引

重建索引會卸除和重新建立索引。視索引類型和資料庫引擎版本而定，重建作業可能會離線或線上執行。離線索引重建所花費的時間通常比線上重建還少，但其會在重建作業期間保留物件層級的鎖定，以防止查詢存取資料表或檢視。

線上索引重建在作業結束之前不需要物件層級的鎖定，此時必須短暫保留鎖定，才能完成重建。視資料庫引擎版本而定，線上索引重建可以當成可繼續的作業來啟動。可繼續的索引重建可以暫停，並保留到該時間點所完成的進度。可繼續的重建作業可以在暫停或中斷之後繼續，或者在已經不需要完成重建時中止。

如需 Transact-SQL 語法，請參閱 ALTER INDEX REBUILD \(部分機器翻譯\)。如需線上索引重建的詳細資訊，請參閱線上執行索引作業。

注意

線上重建索引時，每次修改索引資料行中的資料，都必須更新索引的額外複本。這可能導致資料修改陳述式的效能在線上重建期間稍微降低。

如果線上可繼續的索引作業已暫停，此效能影響會持續存在，直到該可繼續的作業完成或中止為止。如果您不想完成可繼續的索引作業，請將其中止，而不是暫停。

提示

根據可用的資源和工作負載模式，在 ALTER INDEX REBUILD 陳述式中指定高於預設 MAXDOP 值的值，可能會縮短重建的持續時間，代價則是較高的 CPU 使用率。

針對資料列存放區索引，重建會移除索引內所有等級的片段，並根據指定或當下的填滿因數壓縮分頁。指定 ALL 時，會在單一作業中卸除資料表的所有索引，然後加以重建。具備超過 128 個範圍的索引重建時，資料庫引擎會延緩分頁的解除配置作業並取得相關聯的鎖定內容，直到重建完成為止。如需語法範例，請參閱範例：資料列存放區重建。
針對資料行存放區索引，重建會移除片段，將所有差異存放區資料列移至資料行存放區，並實際刪除已標示刪除的資料列。如需語法範例，請參閱範例：資料行存放區重建。

提示

從 SQL Server 2016 (13.x) 開始，通常不需重建資料行存放區索引，這是因為 REORGANIZE 會以線上作業方式執行必要的重建。

使用索引重建從資料損毀中復原

在 SQL Server 2008 (10.0.x) 之前，有時候您可重建資料列存放區非叢集索引，以更正索引中資料損毀所造成的任何不一致情況。

您仍可能透過離線重建非叢集索引來修復非叢集索引的這類不一致情況。不過，您無法利用線上重建索引的方式來修復非叢集索引不一致情況，因為線上重建機制會以現有非叢集索引作為重建基礎，並因而保存不一致的情況。離線重建索引有時會強制掃描叢集索引 (或堆積)，進而用叢集索引或堆積中的資料取代非叢集索引中不一致的資料。

若要確保資料來源為叢集索引或堆積，請卸除並重新建立非叢集索引，不要重建之。與舊版一樣，從備份中還原受影響的資料，即可從不一致情況中復原。不過，藉由離線重建索引或重新建立索引，您可以修復非叢集索引不一致情況。如需詳細資訊，請參閱 DBCC CHECKDB (Transact-SQL)。

自動索引與統計資料管理

使用自適性索引重組等解決方案，為一或多個資料庫自動管理索引片段以及統計資料更新。這項程序會根據索引分散程度與其他參數，自動選擇要進行重建或是重新組織索引，並以線性閾值更新統計資料。

重建和重新組織資料列存放區索引的特定考量

下列情節會自動重建資料表上的所有資料列存放區非叢集索引：

在資料表上建立叢集索引，包括使用 CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON) 以不同的索引碼重新建立叢集索引
卸除使資料表儲存為堆積的叢集索引

下列情節不會自動在相同的資料表上重建所有資料列存放區非叢集索引：

重建叢集索引
變更叢集索引儲存體，例如套用資料分割配置，或將叢集索引移至不同的檔案群組

重要

如果索引所在的檔案群組離線或唯讀，便無法重新組織或重建該索引。當指定了 ALL 關鍵字，且有一個或多個索引在離線或唯讀檔案群組中，陳述式會失敗。

發生索引重建時，實體媒體必須具有足夠空間來儲存兩份索引複本。重建完成時，資料庫引擎會刪除原始索引。

當搭配 ALTER INDEX ... REORGANIZE 陳述式指定 ALL 時，會重新組織資料表上的叢集、非叢集和 XML 索引。

重建或重新組織小型資料列存放區索引通常不會減少片段。使用 SQL Server 2014 (12.x) (含) 之前的版本時，SQL Server 資料庫引擎會使用混合範圍配置空間。因此，小型索引的分頁有時會儲存在混合範圍內，並導致這類索引片段化。混合範圍最多可由八個物件所共用，所以當重新組織或重建索引之後，小型索引中的片段可能不會減少。

重建資料行存放區索引的特定考量

重建資料行存放區索引時，資料庫引擎會從原始資料行存放區索引讀取所有資料，包括差異存放區。系統會將資料合併為新的資料列群組，並將所有資料列群組壓縮為資料行存放區。資料庫引擎會透過實際刪除已經標示刪除的資料列，以重組資料行存放區。

注意

從 SQL Server 2019 (15.x) 開始，背景合併工作將會協助 Tuple Mover，該工作會自動壓縮已存在一段時間的較小已開啟差異存放區資料列群組 (由內部閾值決定)，或合併已刪除大量資料列的已壓縮資料列群組。這可改善一段時間的資料行存放區索引品質。如需資料行存放區詞彙與概念的詳細資訊，請參閱資料行存放區索引：概觀。

重建分割區，而非整個資料表

如果索引很大，重建整個資料表將花上很長的時間，而且需要足夠的磁碟空間來儲存重建期間額外的整個索引副本。

針對資料分割資料表，如果片段只存在於部分分割 (例如在 UPDATE、DELETE 或 MERGE 陳述式影響大量資料列的分割)，則無須重建整個資料行存放區索引。

在載入或修改資料後重建分割，能確保所有資料儲存在資料行存放區內壓縮的資料列群組中。資料載入流程用小於 102,400 資料列的批次將資料插入分割時，分割可能會在差異存放區中產生多個開放資料列群組。重建會將所有差異存放區資料列移至資料行存放區內已壓縮的資料列群組。

重新組織資料行存放區索引的特定考量

重新組織資料行存放區索引時，資料庫引擎會以壓縮資料列群組的方式，將差異存放區內每個關閉的資料列群組壓縮至資料行存放區中。從 SQL Server 2016 (13.x) 開始且在 Azure SQL Database 中，REORGANIZE 命令會在線上執行下列額外的重組最佳化：

當 10% 或更多資料列已經以邏輯方式刪除時，會實際將資料列從資料列群組移除。舉例來說，如果具有 1 百萬個資料列的已壓縮資料列群組刪除 100,000 個資料列，則資料庫引擎會移除已刪除的資料列，並將 900,000 個資料列重新壓縮成資料列群組，藉此減少儲存體磁碟使用量。
合併一或多個壓縮的資料列群組，將每個資料列群組的資料列數目最多提高至 1,048,576 個資料列的上限。舉例來說，如果您大量插入五個批次 (每批次有 102,400 個資料列)，則您會得到五個壓縮的資料列群組。如果您執行 REORGANIZE，這些資料列群組會合併為一個具備 512,000 個資料列的壓縮資料列群組。這是假設沒有任何目錄大小或記憶體限制的情況。
資料庫引擎會嘗試合併至少 10% 的資料列被其他資料列群組標示為刪除的資料列群組。舉例來說，資料列群組 1 經過壓縮後具有 500,000 個資料列，而資料列群組 21 經過壓縮後有 1,048,576 個資料列。資料列群組 21 中 60% 的資料列標示為刪除，剩下 409,830 個資料列。資料庫引擎會優先合併這兩個資料列群組，以壓縮成一個包含 909,830 個資料列的新資料列群組。

執行資料負載後，您在差異存放區中可能有多個小型的資料列群組。您可以使用 ALTER INDEX REORGANIZE 將這些資料列群組強制合併為資料行存放區，然後將較小的已壓縮資料列群組合併為較大的已壓縮資料列群組。重新組織作業也會移除已從資料行存放區標示為刪除的資料列。

注意

使用 Management Studio 來重新組織資料行存放區索引會將已壓縮的資料列群組合併在一起，但不會強制將所有資料列群組壓縮到資料行存放區。系統會壓縮已關閉的資料列群組，但不會將開放資料列群組壓縮到資料行存放區。若要強制壓縮所有資料列群組，請用包含 COMPRESS_ALL_ROW_GROUPS = ON 的 Transact-SQL 範例。

執行索引維護前的考量要點

藉由重新組織或重建索引來執行的索引維護，需要耗用大量資源。這會導致 CPU 使用率、使用的記憶體以及儲存體輸入/輸出量大幅提升。然而，根據資料庫工作負載和其他因素，其提供的效益有可能至關重要，也可能微乎其微。

為避免不必要的資源使用率，請勿隨意執行索引維護。反之，索引維護帶進的效能效益應根據每個工作負載使用建議策略所得的經驗判斷，並與為達成這些權益造成的資源成本，以及對工作負載造成的影響兩相權衡。

索引片段化狀況明顯，或分頁密度低落時，透過重新組織或重建索引較有可能獲得效能上的效益。不過，要考量的重點不只如此。查詢模式 (交易處理與分析和報告)、儲存體子系統行為、可用記憶體以及一段時間內資料庫引擎的改善情況等因素都必須納入考慮。

重要

是否應執行索引維護，應在考量過每個工作負載特定內容的諸多要素 (包括維護的資源成本) 之後再決定。請勿單憑固定的片段或分頁密度閾值妄下決斷。

索引重建積極一面的副作用

客戶通常會在索引重建後發現效能有所改善。然而，在許多案例中，所謂的改善其實與減少片段或增加分頁密度無關。

索引重建有個重要的優勢，那就是會透過掃描索引中所有的資料列，更新索引內索引鍵資料行的統計資料。這麼做相當於執行 UPDATE STATISTICS ... WITH FULLSCAN，跟預設樣本化的統計資料更新相比，統計資料能保持最新狀態，有時品質還能因此改善。更新統計資料時，參考統計資料的查詢計劃會重新編譯。如果先前的查詢計劃由於統計資料過時、統計資料取樣比率不足或其他原因未臻完善，則重新編譯的計劃通常會有較佳表現。

客戶很容易誤將這項改善歸功於索引重建本身，認為這是片段狀況減少與分頁密度增加的結果。實際上，只要更新統計資料，往往就能以更低廉的資源成本取得相同的效益，無須重建索引。

提示

更新統計資料花費的資源成本，跟索引重建相比低廉不少，且作業往往只需幾分鐘就能完成。而索引重建可能需耗時數個小時。

索引維護策略

Microsoft 建議客戶考慮並採用下列索引維護策略：

勿假設索引維護一律能為工作負載帶來顯著的改善。
衡量重新組織或重建索引為工作負載中查詢效能帶來的具體影響。查詢存放區是使用 A/B 測試技術衡量「維護前」和「維護後」效能的好方法。
如果您發現重建索引使效能有所提升，不妨試著以更新統計資料取而代之。這有可能帶來類似的改善。若是如此，您可能無須頻繁重建索引 (甚至完全不用)，只要定期更新統計資料即可。針對部分統計資料，您可能需要透過 WITH SAMPLE ... PERCENT 或 WITH FULLSCAN 子句增加取樣比率 (這種情況並不常見)。
監視一段時間內的索引片段狀況和分頁密度，判斷這些值攀升或下滑的趨勢與查詢效能是否相互關聯。如果片段偏高或分頁密度較低時，效能降低到無法接受的程度，請重新組織或重建索引。通常只要重新組織或重建效能降低之查詢使用的特定索引，就足以改善情況。這能免去維護資料庫中所有索引而導致的高額資源成本。
掌握片段/分頁密度和效能間的相互關聯，也有助判斷索引維護的頻率。請勿假設維護作業必須於固定的排程內進行。較佳的策略是監視片段和分頁密度，然後在效能降低到無法接受的程度以前，視需求執行索引維護。
如果您已認定需要索引維護且資源成本可接受，請盡可能在資源使用率較低的時段內執行維護。
定期執行測試，因為資源使用模式很可能隨時間而異。

Azure SQL Database 和 Azure SQL 受控執行個體內的索引維護

除了上述考量要點與策略之外，Azure SQL Database 和 Azure SQL 受控執行個體尤其要考慮索引維護的成本和效益。客戶應只在出現確切需求時執行，同時考量以下要點：

Azure SQL Database 和 Azure SQL 受控執行個體會實作資源治理，以根據佈建的定價層設定 CPU、記憶體和輸入/輸出使用量的界限。這些界限適用於所有使用者工作負載，包括索引維護。如果所有工作負載累積的資源耗用量逼近資源界限，則基於資源競爭，重建或重新組織作業便很可能降低其他工作負載的效能。舉例來說，大量資料載入的速度可能減慢，因為交易記錄輸入/輸出已因同時進行的索引重建達到 100%。在 Azure SQL 受控執行個體中，可透過在不同的 Resource Governor 工作負載群組，利用受限制的資源配置來降低這項影響，代價是索引維護時間延長。
為了減少支出，客戶常會以最低限度的資源空餘空間來佈建資料庫、彈性集區和受控執行個體。他們會選擇足以支援應用程式工作負載的定價層。為了容納因索引維護大幅增加的資源使用量，同時保持應用程式效能不致降低，客戶可能必須佈建更多資源並增加支出，而這未必能提升應用程式的效能。
在彈性集區中，資源會在集區的所有資料庫中共用。就算有特定資料庫閒置，針對該資料庫執行索引維護，也可能影響在相同集區內其他資料庫上同時執行之應用程式的工作負載。如需詳細資訊，請參閱密集彈性集區中的資源管理。
針對多數 Azure SQL Database 和 Azure SQL 受控執行個體使用的儲存體型別，循序輸入/輸出和隨機輸入/輸出的效能並無區別。二者都能減少索引片段化對查詢效能的影響。
使用讀取縮放或異地複寫複本時，複本的資料延遲往往會在主要複本執行索引維護時增加。如果異地複本佈建時使用的資源，不足以維持索引維護時多產生的交易記錄，則很有可能大幅落後主要複本，進而使系統重新植入。這會導致複本在重新植入完成之前無法使用。不僅如此，在進階和業務關鍵的服務層級中，用於高可用性的複本同樣可能在索引維護期間大幅落後主要複本。如果索引維護期間或甫結束便需要容錯移轉，則可能花費比預期更長的時間。
如果索引重建在主要複本上執行，同時在可讀取的複本上執行長時間執行的查詢，則該查詢可能會自動終止，以免阻擋複本上的重做執行緒。

有些專特但不常見的情節，會需要在 Azure SQL Database 和 Azure SQL 受控執行個體上執行一次性或定期的索引維護：

增加分頁密度並減少資料庫上使用的空間，從而保持在定價層的大小上限之內。這麼做就不必擴大到大小上限更高，但更高額的定價層。
如果有必要壓縮檔案，則在壓縮資料檔案之前重建或重新組織索引，能夠增加分頁密度。這能加快壓縮作業的速度，因為需要移動的分頁減少了。如需詳細資訊，請造訪：
- 在 Azure SQL 資料庫中管理資料庫的檔案空間
- 在 Azure SQL 受控執行個體中管理資料庫的檔案空間

提示

如果您認定必須為 Azure SQL Database 和 Azure SQL 受控執行個體工作負載執行索引維護，則應重新組織索引，或使用連線索引重建。這能讓查詢工作負載在索引重建時存取資料表。

此外，保持作業持續進行，能讓您在計劃性或非計劃性資料庫容錯移轉導致作業中斷時，免去從頭重新啟動的麻煩。使用可繼續的索引作業，在索引龐大時尤其重要。

提示

離線索引作業通常能比連線作業更快完成。查詢不會於作業期間存取資料表時，則應採用離線作業。在循序 ETL 流程中將資料載入暫存表格就是一例。

限制事項

具有超過 128 個範圍的資料列存放區索引將以兩個不同的階段重建：邏輯和實體。在邏輯階段中，索引所使用的現有配置單位將以取消配置標示，並複製和排序資料列，然後移到所建立的新配置單位以儲存重建索引。在實體階段中，會將先前標示為取消配置的配置單位，在背景以短暫的交易實際卸除，而且不需要許多鎖定。如需配置單位的詳細資訊，請參閱分頁與範圍結構指南。

ALTER INDEX REORGANIZE 陳述式需要包含索引的資料檔案具有可用空間，因為作業只能配置在相同檔案上的暫存工作分頁上，而不是在相同檔案群組內的其他檔案中。如果資料檔案在重新組織作業期間空間不足，則即使檔案群組有可用的免費空間，使用者仍會遭遇錯誤 1105：Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup。

當 ALLOW_PAGE_LOCKS 設定為 OFF 時，無法重新組織索引。

在 SQL Server 2017 (14.x) 與更舊版本中，重建叢集資料行存放區索引為離線作業。在進行重建時，資料庫引擎必須取得資料表或分割區上的獨佔鎖定。在重建期間，資料將會離線且無法使用，即便是使用 NOLOCK、讀取認可快照隔離 (RCSI) 或快照隔離也一樣。從 SQL Server 2019 (15.x) 開始，叢集資料行存放區索引可以使用 ONLINE = ON 選項來重建。

警告

您可以對包含超過 1,000 個分割區的資料表，建立及重建不以資料表為準的索引，但不予支援。此做法可能會導致在作業期間效能降低或耗用過多記憶體。 Microsoft 建議當分割區數超過 1,000 個時，才使用對齊的索引。

統計資料的限制

建立或重建索引時，系統會掃描資料表內所有資料列，藉此建立或更新統計資料，這相當於在 CREATE STATISTICS 或 UPDATE STATISTICS 內使用 FULLSCAN 子句。不過，從 SQL Server 2012 (11.x) 開始，並不會在建立或重建資料分割索引之後，透過掃描資料表中的所有資料列來建立或更新統計資料。反之，系統會使用預設取樣比率。如果要在掃描資料表中所有資料列時建立或更新分割區索引的統計資料，請使用 CREATE STATISTICS 或 UPDATE STATISTICS 搭配 FULLSCAN 子句。
同理，若索引的建立或重建作業可繼續，則統計資料會使用預設的取樣比率建立或更新。如果統計資料已使用設為 ON 的 PERSIST_SAMPLE_PERCENT 子句建立或最近更新，則可繼續的索引作業會用已保存的取樣比率建立或更新統計資料。
重新組織索引時，不會更新統計資料。

範例

使用 Transact-SQL 檢查資料行存放區索引的片段和分頁密度

以下範例能判斷目前資料庫中所有資料行存放區索引的平均片段和分頁密度。系統會用 SAMPLED 模式迅速傳回可採取動作的結果。如需更精確的結果，請使用 DETAILED 模式。這麼做需要掃描所有索引頁，且可能花費很長時間。

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

上面的陳述式會傳回如下結果集：

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

如需詳細資訊，請參閱 sys.dm_db_index_physical_stats。

使用 Transact-SQL 檢查資料行存放區索引的片段

以下範例能判斷目前資料庫中，所有具有壓縮資料列群組之資料行存放區索引的平均片段。

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

上面的陳述式會傳回如下結果集：

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

使用 SQL Server Management Studio 維護索引

重新組織或重建索引

在 [物件總管] 中，展開包含您要重新組織其索引之資料表的資料庫。
展開 [資料表] 資料夾。
展開您要重新組織其索引的資料表。
展開 [索引] 資料夾。
以滑鼠右鍵按一下您要重新組織的索引，然後選取 [重新組織] 。
在 [重新組織索引] 對話方塊中，確認 [要重新組織的索引] 方格中有正確索引，然後選取 [確定]。
選取 [壓縮大型物件資料行資料] 核取方塊，可指定一併壓縮包含大型物件 (LOB) 資料的所有頁面。
選取 [確定]。

重新組織資料表中的所有索引

在 [物件總管] 中，展開包含您要重新組織其索引之資料表的資料庫。
展開 [資料表] 資料夾。
展開您要重新組織其索引的資料表。
以滑鼠右鍵按一下 [索引] 資料夾，並選取 [全部重新組織] 。
在 [重新組織索引] 對話方塊中，確認 [要重新組織的索引] 方格中有正確索引。若要從 [要重新組織的索引] 方格中移除索引，請選取索引，然後按下 DELETE 鍵。
選取 [壓縮大型物件資料行資料] 核取方塊，可指定一併壓縮包含大型物件 (LOB) 資料的所有頁面。
選取 [確定]。

使用 Transact-SQL 維護索引

注意

如需更多使用 Transact-SQL 重建或重新組織索引的範例，請參閱 ALTER INDEX 範例 - 資料列存放區索引和 ALTER INDEX 範例 - 資料行存放區索引。

重新組織索引

下列範例會重新組織 AdventureWorks2022 資料庫中 HumanResources.Employee 資料表上的 IX_Employee_OrganizationalLevel_OrganizationalNode 索引。

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

下列範例會重新組織 AdventureWorksDW2022 資料庫中 dbo.FactResellerSalesXL_CCI 資料表上的 IndFactResellerSalesXL_CCI 資料行存放區索引。此命令會將所有已關閉和已開啟的資料列群組強制移動到資料行存放區中。

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

重新組織資料表中的所有索引

下列範例會重新組織 AdventureWorks2022 資料庫中 HumanResources.Employee 資料表上的所有索引。

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

重建索引

下列範例會在 AdventureWorks2022 資料庫的 Employee 資料表上重建單一索引。

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

重建資料表中的全部索引

下列範例會使用 ALL 關鍵字來重建與 AdventureWorks2022 資料庫中資料表相關聯的所有索引。指定三個選項。

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

如需詳細資訊，請參閱 ALTER INDEX。

分享方式：

將索引維護最佳化以改善查詢效能並降低資源耗用量

概念：索引片段和分頁密度

測量索引片段和頁面密度

維護索引的方法：重新組織與重建

重新組織索引

重建索引

使用索引重建從資料損毀中復原

自動索引與統計資料管理

重建和重新組織資料列存放區索引的特定考量

重建資料行存放區索引的特定考量

重建分割區，而非整個資料表

重新組織資料行存放區索引的特定考量

執行索引維護前的考量要點

索引重建積極一面的副作用

索引維護策略

Azure SQL Database 和 Azure SQL 受控執行個體內的索引維護

限制事項

統計資料的限制

範例

使用 Transact-SQL 檢查資料行存放區索引的片段和分頁密度

使用 Transact-SQL 檢查資料行存放區索引的片段

使用 SQL Server Management Studio 維護索引

重新組織或重建索引

重新組織資料表中的所有索引

使用 Transact-SQL 維護索引

重新組織索引

重新組織資料表中的所有索引

重建索引

重建資料表中的全部索引

意見反映

更多資源

分享方式：

將索引維護最佳化以改善查詢效能並降低資源耗用量

概念：索引片段和分頁密度

測量索引片段和頁面密度

維護索引的方法：重新組織與重建

重新組織索引

重建索引

使用索引重建從資料損毀中復原

自動索引與統計資料管理

重建和重新組織資料列存放區索引的特定考量

重建資料行存放區索引的特定考量

重建分割區，而非整個資料表

重新組織資料行存放區索引的特定考量

執行索引維護前的考量要點

索引重建積極一面的副作用

索引維護策略

Azure SQL Database 和 Azure SQL 受控執行個體內的索引維護

限制事項

統計資料的限制

範例

使用 Transact-SQL 檢查資料行存放區索引的片段和分頁密度

使用 Transact-SQL 檢查資料行存放區索引的片段

使用 SQL Server Management Studio 維護索引

重新組織或重建索引

重新組織資料表中的所有索引

使用 Transact-SQL 維護索引

重新組織索引

重新組織資料表中的所有索引

重建索引

重建資料表中的全部索引

相關內容

意見反映

更多資源