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

適用于:SQL Server (所有支援的版本) Azure SQL Database Azure SQL 受控執行個體 Analytics Platform System ( PDW)

本文可協助您決定何時以及如何執行索引維護。 其涵蓋索引片段和頁面密度等概念,以及其對查詢效能和資源耗用量的影響。 它會描述索引維護方法、 重新組織索引重建索引,並建議索引維護策略,以平衡維護所需的資源耗用量的潛在效能改善。

注意

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

概念:索引片段和頁面密度

什麼是 索引片段 ,以及其如何影響效能:

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

    注意

    SQL Server檔使用 B 型樹狀結構一般參考索引一詞。 在資料列存放區索引中,SQL Server實作 B+ 樹狀結構。 這不適用於資料行存放區索引或記憶體內部資料存放區。 如需詳細資訊,請檢閱SQL Server索引架構和設計指南

  • 每當對基礎資料進行插入、更新或刪除作業時,Database Engine 會自動修改索引。 例如,在資料表中加入資料列可能會導致資料 列存放區索引 中的現有頁面分割,讓插入新資料列的空間。 經過一段時間後,這些修改可能會導致索引中的資料散佈在資料庫中, (分散) 。

  • 對於使用完整或範圍索引掃描讀取許多頁面的查詢,大量分散的索引可能會降低查詢效能,因為可能需要額外的 I/O 才能讀取查詢所需的資料。 查詢需要大量的小型 I/O 要求來讀取相同的資料量,而不是少量的 I/O 要求。

  • 當儲存體子系統提供比隨機 I/O 效能更好的循序 I/O 效能時,索引片段可能會降低效能,因為讀取片段索引需要更多隨機 I/O。

什麼是 頁面密度 (也稱為頁面完整度) ,以及其如何影響效能:

  • 資料庫中的每個 頁面 都可以包含一個可變數目的資料列。 如果資料列佔用頁面上的所有空間,頁面密度為 100%。 如果頁面是空的,則頁面密度為 0%。 如果 100% 密度的頁面分割成兩個頁面以容納新的資料列,則兩個新頁面的密度大約是 50%。
  • 當頁面密度偏低時,需要更多頁面才能儲存相同的資料量。 這表示需要更多 I/O 才能讀取和寫入此資料,而且需要更多記憶體來快取此資料。 當記憶體有限時,將會快取查詢所需的較少頁面,因而造成更多的磁片 I/O。 因此,低頁面密度會對效能造成負面影響。
  • 當 Database Engine 將資料列新增至頁面時,如果索引的 填滿因數 設定為 100 (或 0 以外的值,則不會完整填滿頁面,這在此內容中相當於) 。 這會導致頁面密度較低,同樣地會增加 I/O 額外負荷,並對效能造成負面影響。
  • 低頁面密度可能會增加中繼 B 型樹狀結構層級的數目。 這可適當增加在索引掃描和搜尋中尋找分葉層級頁面的 CPU 和 I/O 成本。
  • 當查詢最佳化工具編譯查詢計劃時,它會考慮讀取查詢所需資料的 I/O 成本。 使用低頁面密度時,有更多頁面可供讀取,因此 I/O 的成本較高。 這可能會影響查詢計劃選擇。 例如,由於頁面分割而隨時間減少頁面密度,優化器可能會針對相同的查詢編譯不同的計畫,並使用不同的效能和資源耗用量設定檔。

秘訣

在許多工作負載中,增加頁面密度會產生比減少片段更高的正面效能影響。

為了避免不必要地降低頁面密度,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 繼續。

  • 針對 資料列存放區索引,Database Engine 只會在資料表和檢視表上重組叢集和非叢集索引的分葉層級,方法是實際重新排序分葉層級頁面,以符合分葉節點的邏輯順序 (由左至右) 。 重新組織也會壓縮索引頁,使頁面密度等於索引的 填滿因數 。 若要檢視填滿因數設定,請使用 sys.indexes。 如需語法範例,請參閱 範例 - 資料列存放區重新組織
  • 使用 資料行存放區索引時,差異存放區最終可能會在插入、更新和刪除一段時間內的資料之後,加上多個小型資料列群組。 重新組織資料行存放區索引會將差異存放區資料列群組強制將資料列群組合並到資料行存放區中的壓縮資料列群組,並將較小的壓縮資料列群組合並成較大的資料列群組。 重新組織作業也會實際移除資料行存放區中標示為已刪除的資料列。 重新組織資料行存放區索引可能需要額外的 CPU 資源來壓縮資料,這可能會在作業執行時降低整體系統效能。 不過,一旦壓縮資料,查詢效能就會改善。 如需語法範例,請參閱 範例 - 資料行存放區重新組織

注意

從 2019 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 或更多範圍的索引時,Database Engine 會延遲頁面解除配置,並取得相關聯的鎖定,直到重建完成為止。 如需語法範例,請參閱 範例 - 資料列存放區重建

  • 針對 資料行存放區索引,重建會移除片段、將任何差異存放區資料列移至資料行存放區,並實際刪除標示為要刪除的資料列。 如需語法範例,請參閱 範例 - 資料行存放區重建

    秘訣

    從 SQL Server 2016 (13.x) 開始,通常不需要重建資料行存放區索引,因為 REORGANIZE 會以線上作業的形式執行重建的基本概念。

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

在舊版的 SQL Server 中,您有時可能會重建資料列存放區非叢集索引,以更正索引中的資料損毀所造成的不一致。

從 SQL Server 2008 開始,您仍可藉由離線重建非叢集索引來修復非叢集索引中的這類不一致。 不過,您無法透過線上重建索引來修復非叢集索引不一致的情況,因為線上重建機制會使用現有的非叢集索引作為重建的基礎,因而不一致。 離線重建索引有時會強制掃描叢集索引 (或堆積) ,因此請將非叢集索引中的資料取代為叢集索引或堆積中的資料不一致。

若要確保叢集索引或堆積作為資料來源使用,請卸載並重新建立非叢集索引,而不是重建它。 如同舊版,建議您從備份還原受影響的資料,以從不一致的情況復原;不過,您可以藉由離線重建索引或重新建立索引,來修復非叢集索引不一致。 如需詳細資訊,請參閱 DBCC CHECKDB (Transact-SQL)

自動索引與統計資料管理

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

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

下列案例會導致資料表上的所有資料列存放區非叢集索引自動重建:

  • 在資料表上建立叢集索引,包括使用不同的索引鍵重新建立叢集索引 CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
  • 卸載叢集索引,這會導致資料表儲存為堆積

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

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

重要事項

如果索引所在的檔案群組離線或唯讀,就無法重新組織或重建索引。 當指定關鍵字 ALL 且一或多個索引位於離線或唯讀檔案群組時,語句會失敗。

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

使用 ALTER INDEX ... REORGANIZE 語句、叢集、非叢集和資料表上的 XML 索引來指定 時 ALL ,會重新組織資料表。

重建或重新組織小型資料列存放區索引可能不會減少片段。 SQL Server 2014 (12.x) ,SQL Server Database Engine 最多會使用混合範圍配置空間。 因此,小型索引的頁面有時會儲存在混合範圍上,這隱含地讓這類索引分散。 混合範圍最多可由八個物件所共用,所以當重新組織或重建索引之後,小型索引中的片段可能不會減少。

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

重建資料行存放區索引時,Database Engine 會從原始資料行存放區索引讀取所有資料,包括差異存放區。 它會將資料合併成新的資料列群組,並將所有資料列群組壓縮成資料行存放區。 Database Engine 會藉由實際刪除標示為已刪除的資料列,來重組資料行存放區。

注意

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

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

如果索引很大,重建整個資料表需要很長的時間,而且需要足夠的磁碟空間,才能在重建期間儲存整個索引的額外複本。

對於資料分割資料表,如果片段只存在於某些資料分割中,則您不需要重建整個資料行存放區索引,例如,在 、 DELETEMERGE 語句影響大量資料列的資料分割中 UPDATE

載入或修改資料之後重建資料分割可確保所有資料都儲存在資料行存放區中的壓縮資料列群組中。 當資料載入程式使用小於 102,400 個數據列的批次將資料插入資料分割中時,資料分割最後可能會有多個差異存放區中開啟的資料列群組。 重建會將所有差異存放區資料列移至資料行存放區中的壓縮資料列群組。

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

重新組織資料行存放區索引時,Database Engine 會將差異存放區中的每個已關閉資料列群組壓縮成資料行存放區,作為壓縮的資料列群組。 從 SQL Server 2016 (13.x) 和 Azure SQL Database 開始, REORGANIZE 命令會在線上執行下列額外的重組優化:

  • 在邏輯上刪除 10% 以上的資料列時,實際從資料列群組中移除資料列。 例如,如果 1 百萬個數據列的壓縮資料列群組已刪除 100,000 個數據列,Database Engine 將會移除已刪除的資料列,並重新壓縮具有 900,000 個數據列的資料列群組,以減少儲存體使用量。
  • 結合一或多個壓縮的資料列群組,以增加每個資料列群組的資料列,最多可達 1,048,576 個數據列。 例如,如果您為每個批次大量插入 102,400 個數據列的五個批次,您將會收到五個壓縮的資料列群組。 如果您執行 REORGANIZE,這些資料列群組將會合並成一個壓縮的資料列群組,其中包含 512,000 個數據列。 這是假設沒有任何目錄大小或記憶體限制的情況。
  • Database Engine 會嘗試合併資料列群組,其中 10% 或更多資料列已與其他資料列群組一起標示為已刪除。 例如,資料列群組 1 會壓縮,且有 500,000 個數據列,而資料列群組 21 則壓縮,且具有 1,048,576 個數據列。 資料列群組 21 有 60% 的資料列標示為已刪除,這會留下 409,830 個數據列。 Database Engine 偏好合併這兩個數據列群組,以壓縮具有 909,830 個數據列的新資料列群組。

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

注意

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

在執行索引維護之前要考慮的事項

重新組織或重建索引所執行的索引維護會耗用大量資源。 這會導致 CPU 使用率、記憶體使用量和儲存體 I/O 大幅增加。 不過,視資料庫工作負載和其他因素而定,它提供的範圍從重要到減去的範圍。

為了避免對查詢工作負載造成負面影響的不必要的資源使用率,Microsoft 不建議不小心執行索引維護。 相反地,應該使用建議 的策略來判斷每個工作負載的效能優勢,並針對達到這些優點所需的資源成本和工作負載影響進行衡量。

當索引高度分散或頁面密度偏低時,看到重新組織或重建索引的效能優勢的可能性較高。 不過,這不是唯一要考慮的事項。 查詢模式等因素 (交易處理與分析與報告) 、儲存體子系統行為、可用記憶體,以及一段時間的資料庫引擎改善都會扮演角色。

重要事項

考慮每個工作負載特定內容中的多個因素之後,應該進行索引維護決策,包括維護的資源成本。 它們不應單獨以固定片段或頁面密度臨界值為基礎。

索引重建的正面副作用

客戶通常會在重建索引之後觀察到效能改善。 不過,在許多情況下,這些改善與減少片段或增加頁面密度無關。

索引重建有一個重要優點:它會藉由掃描索引中的所有資料列來更新索引鍵資料行的 統計資料 。 這相當於執行 UPDATE STATISTICS ... WITH FULLSCAN ,這會讓統計資料變成目前狀態,有時會改善其品質,相較于預設取樣統計資料更新。 更新統計資料時,會重新編譯參考它們的查詢計劃。 如果查詢的上一個計畫因為過時統計資料、統計資料取樣比例不足,或其他原因而無法優化,則重新編譯的計畫通常會執行得更好。

客戶通常會不正確地將此改進屬性設定為索引重建本身,導致片段減少並增加頁面密度。 事實上,藉由 更新統計資料 ,而不是重建索引,通常可以以較便宜的資源成本達成相同的優點。

秘訣

相較于索引重建,更新統計資料的資源成本很小,而且作業通常會在幾分鐘內完成,而不是索引重建所需的小時。

索引維護策略

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

  • 請勿假設索引維護一律會明顯改善您的工作負載。
  • 測量重新組織或重建索引對工作負載中查詢效能的特定影響。 查詢存放區是使用A/B 測試技術測量「維護前」和「維護後」效能的好方法。
  • 如果您發現重建索引可改善效能,請嘗試將它取代為更新統計資料。 這可能會導致類似的改進。 在此情況下,您可能不需要經常重建索引,或完全不需要重建索引,而是可以執行定期統計資料更新。 對於某些統計資料,您可能需要使用 WITH SAMPLE ... PERCENTWITH FULLSCAN 子句來增加取樣比例, (這不是常見的) 。
  • 監視一段時間的索引片段和頁面密度,以查看這些值是否相互關聯,以及向上或減少,以及查詢效能。 如果較高的片段或較低的頁面密度降低無法接受的效能,請重新組織或重建索引。 通常足以重新組織或重建效能降低之查詢所使用的特定索引。 這可避免維護資料庫中每個索引的資源成本較高。
  • 建立片段/頁面密度與效能之間的相互關聯,也可讓您判斷索引維護的頻率。 請勿假設維護必須在固定排程上執行。 更好的策略是監視片段和頁面密度,並在效能降低無法接受之前視需要執行索引維護。
  • 如果您已判斷需要索引維護,而且其資源成本是可接受的,請在低資源使用量時間執行維護,如果有的話,請記住資源使用量模式可能會隨著時間變更。

Azure SQL 資料庫中的索引維護與Azure SQL 受控執行個體

除了上述考慮和策略之外,在 Azure SQL Database 和 Azure SQL 受控執行個體 考慮索引維護的成本和優點特別重要。 只有在有示範的需求時,客戶才應該執行它,並考慮下列幾點。

  • Azure SQL 資料庫和Azure SQL 受控執行個體實作資源控管,以根據布建的定價層設定 CPU、記憶體和 I/O 耗用量的界限。 這些界限適用于所有使用者工作負載,包括索引維護。 如果所有工作負載的累計資源耗用量接近資源界限,則重建或重新組織作業可能會因為資源爭用而降低其他工作負載的效能。 例如,大量資料載入可能會變慢,因為交易記錄 I/O 因為同時重建索引而處於 100%。 在Azure SQL 受控執行個體中,藉由在具有限制資源配置的個別Resource Governor工作負載群組中執行索引維護,來降低此影響,但代價是延長索引維護持續時間。
  • 為了節省成本,客戶通常會以最少的資源清理布建資料庫、彈性集區和受控實例。 定價層會選擇足以用於應用程式工作負載。 為了因索引維護而不需要降低應用程式效能而大幅增加資源使用量,客戶可能必須布建更多資源並增加成本,而不需要改善應用程式效能。
  • 在彈性集區中,資源會跨集區中的所有資料庫共用。 即使特定資料庫閒置,在該資料庫上執行索引維護可能會影響相同集區中其他資料庫中同時執行的應用程式工作負載。 如需詳細資訊,請參閱 密集彈性集區中的資源管理
  • 對於Azure SQL資料庫和Azure SQL 受控執行個體中使用的大部分儲存體類型,循序 I/O 和隨機 I/O 之間的效能沒有任何差異。 這可減少索引片段對查詢效能的影響。
  • 使用 讀取向外延展異地複 寫複本時,複本上的資料延遲通常會在主要複本上執行索引維護時增加。 如果異地複本布建的資源不足,無法維持索引維護所造成的交易記錄產生增加,它可能會落後于主要複本,導致系統重新設定。 這會使複本無法使用,直到重新建立完成為止。 此外,在進階和業務關鍵服務層級中,用於高可用性的複本在索引維護期間可能遠落後于主要複本。 如果在索引維護期間或稍後需要容錯移轉,則可能需要比預期更長的時間。
  • 如果在主要複本上執行索引重建,且長時間執行的查詢同時在可讀取的複本上執行,查詢可能會自動終止,以防止封鎖複本上的重做執行緒。

Azure SQL 資料庫和Azure SQL 受控執行個體可能需要一次性或定期索引維護時,有一些特定但罕見的情況:

  • 可能需要索引維護,才能增加頁面密度並減少資料庫中的已使用空間,因此請保持在定價層的大小限制內。 這可避免必須相應增加為具有較高大小限制的較高定價層。
  • 如果需要 壓縮資料檔案,在壓縮檔案之前重建或重新組織索引將會增加頁面密度。 這可讓壓縮作業更快速,因為它需要移動較少的頁面。

秘訣

如果您判斷Azure SQL資料庫和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) ,重建叢集資料行存放區索引是離線作業。 發生重建時,Database Engine 必須取得資料表或分割區上的獨佔鎖定。 在重建期間,資料將會離線且無法使用,即便是使用 NOLOCK、讀取認可快照隔離 (RCSI) 或快照隔離也一樣。 從 SQL Server 2019 (15.x) 開始,可以使用 選項重建 ONLINE = ON 叢集資料行存放區索引。

警告

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

統計資料限制

  • 建立重建索引時,會藉由掃描資料表中的所有資料列來建立或更新統計資料,這相當於在 或 UPDATE STATISTICS 中使用 FULLSCAN 子句。 CREATE STATISTICS 不過,從 SQL Server 2012 (11.x) 開始,建立或重建資料分割索引時,不會藉由掃描資料表中的所有資料列來建立或更新統計資料。 相反地,會使用預設取樣比例。 若要藉由掃描資料表中的所有資料列來建立或更新資料分割索引的統計資料,請使用 CREATE STATISTICSUPDATE STATISTICS 搭配 FULLSCAN 子句。
  • 同樣地,當索引建立或重建作業可繼續時,系統會使用預設取樣比例來建立或更新統計資料。 如果統計資料已建立或上次更新,且 PERSIST_SAMPLE_PERCENT 子句設定為 ON ,則可繼續的索引作業會使用持續性取樣比例來建立或更新統計資料。
  • 重新組織索引時,不會更新統計資料。

範例

使用 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 維護索引

若要重新組織或重建索引

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

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

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

使用 Transact-SQL 維護索引

注意

如需使用 Transact-SQL 重建或重新組織索引的詳細資訊,請參閱 ALTER INDEX 範例 - 資料列存放區索引ALTER INDEX 範例 - 資料行存放區索引

若要重新組織索引

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

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

下列範例會重新組織 AdventureWorksDW2016 資料庫中 dbo.FactResellerSalesXL_CCI 資料表上的 IndFactResellerSalesXL_CCI 資料行存放區索引。

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

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

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

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

若要重建索引

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

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

若要重建資料表中全部的索引

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

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

如需詳細資訊,請參閱 ALTER INDEX

另請參閱