線上索引作業的指導方針Guidelines for online index operations

適用於: 是SQL Server是Azure SQL Database否Azure Synapse Analytics (SQL DW)否平行處理資料倉儲APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

當您執行線上索引作業時,下列指導方針將適用:When you perform online index operations, the following guidelines apply:

  • 當基礎資料表包含下列大型物件 (LOB) 資料類型時,必須離線建立、重建或卸除叢集索引: imagentexttextClustered indexes must be created, rebuilt, or dropped offline when the underlying table contains the following large object (LOB) data types: image, ntext, and text.
  • 當資料表包含 LOB 資料類型,但這些資料行並未在索引定義中當做索引鍵或非索引鍵 (內含) 資料行使用時,您可以在線上建立非唯一的非叢集索引。Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns.
  • 您無法在線上建立、重建或卸除本機暫存資料表的索引。Indexes on local temp tables cannot be created, rebuilt, or dropped online. 此限制不適用於全域暫存資料表上的索引。This restriction does not apply to indexes on global temp tables.
  • 可以從非預期的失敗、資料庫容錯移轉或 PAUSE 命令之後的停止處繼續索引。Indexes can be resumed from where it stopped after an unexpected failure, database failover, or a PAUSE command. 請參閱 Create IndexAlter IndexSee Create Index and Alter Index.

注意

並非所有版本的 MicrosoftMicrosoft SQL ServerSQL Server 都可以使用線上索引作業。Online index operations are not available in every edition of MicrosoftMicrosoft SQL ServerSQL Server. 如需 SQL ServerSQL Server 版本所支援的功能清單,請參閱版本支援的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Features supported by editions.

下表顯示可在線上執行的索引作業、從這些線上作業排除的索引,以及可繼續的索引限制。The following table shows the index operations that can be performed online, the indexes that are excluded from these online operations, and resumable index restrictions. 也包含其他限制。Additional restrictions are also included.

線上索引作業Online index operation 排除索引Excluded indexes 其他限制Other restrictions
ALTER INDEX REBUILDALTER INDEX REBUILD 已停用叢集索引或已停用索引檢視Disabled clustered index or disabled indexed view

XML 索引XML index

資料行存放區索引Columnstore index

本機暫存資料表上的索引Index on a local temp table
當資料表包含排除索引時,指定關鍵字 ALL 可能導致作業失敗。Specifying the keyword ALL may cause the operation to fail when the table contains an excluded index.

重建已停用索引的其他限制也適用。Additional restrictions on rebuilding disabled indexes apply. 如需詳細資訊,請參閱 停用索引和條件約束For more information, see Disable Indexes and Constraints.
CREATE INDEXCREATE INDEX XML 索引XML index

在檢視上的初始唯一叢集索引Initial unique clustered index on a view

本機暫存資料表上的索引Index on a local temp table
CREATE INDEX WITH DROP_EXISTINGCREATE INDEX WITH DROP_EXISTING 已停用叢集索引或已停用索引檢視Disabled clustered index or disabled indexed view

本機暫存資料表上的索引Index on a local temp table

XML 索引XML index
DROP INDEXDROP INDEX 停用的索引Disabled index

XML 索引XML index

非叢集索引Nonclustered index

本機暫存資料表上的索引Index on a local temp table
不能在單一陳述式中指定多個索引。Multiple indexes cannot be specified within a single statement.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY 或 UNIQUE)ALTER TABLE ADD CONSTRAINT (PRIMARY KEY or UNIQUE) 本機暫存資料表上的索引Index on a local temp table

叢集索引Clustered index
一次僅允許一個子子句 (Subclause)。Only one subclause is allowed at a time. 例如,您無法在同一個 ALTER TABLE 陳述式中加入和卸除 PRIMARY KEY 或 UNIQUE 條件約束。For example, you cannot add and drop PRIMARY KEY or UNIQUE constraints in the same ALTER TABLE statement.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY 或 UNIQUE)ALTER TABLE DROP CONSTRAINT (PRIMARY KEY or UNIQUE) 叢集索引Clustered index

處理線上索引作業時,不能修改、截斷或卸除基礎資料表。The underlying table cannot be modified, truncated, or dropped while an online index operation is in process.

當您建立或卸除叢集時,指定的線上選項設定 (ON 或 OFF) 會套用到必須重建的任何非叢集索引。The online option setting (ON or OFF) specified when you create or drop a clustered index is applied to any nonclustered indexes that must be rebuilt. 例如,若是使用 CREATE INDEX WITH DROP_EXISTING, ONLINE=ON 線上建立叢集索引,那麼也會線上重建所有相關聯的非叢集索引。For example, if the clustered index is built online by using CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, all associated nonclustered indexes are re-created online also.

線上建立或重建 UNIQUE 索引時,此索引產生器與並行使用者交易可能嘗試要插入同一個索引鍵,因此而違反了唯一性。When you create or rebuild a UNIQUE index online, the index builder and a concurrent user transaction may try to insert the same key, therefore violating uniqueness. 若在原始資料列從來源資料表移動到新索引之前,將使用者輸入的資料列插入至新索引(目標),線上索引作業將失敗。If a row entered by a user is inserted into the new index (target) before the original row from the source table is moved to the new index, the online index operation will fail.

雖然這種情形並不常見,但是由於使用者或應用程序活動的原因,線上索引作業與資料庫更新互動時,即會導致死結。Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. 在這些極少數情況下, SQL Server Database EngineSQL Server Database Engine 將選擇使用者或應用程序活動作為死結的犧牲者。In these rare cases, the SQL Server Database EngineSQL Server Database Engine will select the user or application activity as a deadlock victim.

只有在建立多個新的非叢集索引或重新組織非叢集索引時,才能在同一個資料表或檢視上執行並行線上索引 DDL 作業。You can perform concurrent online index DDL operations on the same table or view only when you are creating multiple new nonclustered indexes, or reorganizing nonclustered indexes. 同時執行的所有其他線上索引作業都會失敗。All other online index operations performed at the same time fail. 例如,在同一個資料表中線上重建現有索引時,是無法線上建立新的索引。For example, you cannot create a new index online while rebuilding an existing index online on the same table.

如果索引包含大型物件類型的資料行,且在同一交易中另有更新作業早於線上作業,便無法執行該線上作業。An online operation cannot be performed when an index contains a column of the large object type, and in the same transaction there are update operations before this online operation. 若要解決此問題,請將線上作業移出交易之外,或使其比交易中的任何更新都更早執行。To work around this issue, place the online operation outside the transaction or place it before any updates in the transaction.

磁碟空間考量因素Disk space considerations

線上索引作業所需的磁碟空間需求高於離線索引作業。Online index operations require more disk space requirements than offline index operations.

  • 在索引建立和索引重建作業期間,所建置 (或重建) 的索引都需要額外的空間。During index creation and index rebuild operations, additional space is required for the index being built (or rebuilt).
  • 此外,暫存對應索引需要磁碟空間。In addition, disk space is required for the temporary mapping index. 此暫存索引用於建立、重建或卸除叢集索引的線上索引作業。This temporary index is used in online index operations that create, rebuild, or drop a clustered index.
  • 線上卸除叢集索引與線上建立 (或重建) 叢集索引需要一樣多的磁碟空間。Dropping a clustered index online requires as much space as creating (or rebuilding) a clustered index online.

如需詳細資訊,請參閱 Disk Space Requirements for Index DDL OperationsFor more information, see Disk Space Requirements for Index DDL Operations.

效能考量Performance considerations

雖然線上索引作業允許並行使用者更新活動,但是若更新活動負載繁重,此索引作業將需要更長時間。Although online index operations permit concurrent user update activity, the index operations will take longer if the update activity is very heavy. 一般而言,無論並行更新活動的程度,線上索引作業都將低於同等的離線索引作業。Typically, online index operations will be slower than equivalent offline index operations regardless of the concurrent update activity level.

由於線上索引作業期間都會維護來源與目標結構,因此會增加插入、更新與刪除交易時所耗用的資源,甚至可能加倍。Because both the source and target structures are maintained during the online index operation, the resource usage for insert, update, and delete transactions is increased, potentially up to double. 這在索引作業期間可能導致效能降低與資源過度耗用,尤其是 CPU 時間。This could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation. 線上索引作業會完整記錄下來。Online index operations are fully logged.

儘管我們推薦線上作業,但您應該評估您的環境與特定要求。Although we recommend online operations, you should evaluate your environment and specific requirements. 離線執行索引作業可能會是最佳方式。It may be optimal to run index operations offline. 若要達到這種方式,在作業期間,使用者僅能有限地存取資料,但是將更快完成作業且使用較少的資源。In doing this, users have restricted access to the data during the operation, but the operation finishes faster and uses fewer resources.

在執行 SQL Server 2016 的多處理器電腦上,索引陳述式可能會如同其他查詢,使用更多處理器來執行與索引陳述式建立關聯的掃描和排序作業。On multiprocessor computers that are running SQL Server 2016, index statements may use more processors to perform the scan and sort operations associated with the index statement just like other queries do. 您可以使用 MAXDOP 索引選項控制線上索引作業專用的處理器數目。You can use the MAXDOP index option to control the number of processors dedicated to the online index operation. 以此方式,您就可以平衡索引作業所使用的資源以及使用者並行所使用的資源。In this way, you can balance the resources that are used by index operation with those of the concurrent users. 如需詳細資訊,請參閱 設定平行索引作業For more information, see Configure Parallel Index Operations. 如需支援平行索引作業之 SQL Server 版本的詳細資訊,請參閱版本支援的功能For more information about the editions of SQL Server that support Parallel indexed operations, see Features Supported by editions.

因為索引作業的最終階段會保留 S-lock 或 Sch-M 鎖定,所以在明確的使用者交易 (例如 BEGIN TRANSACTION...COMMIT 區塊) 內執行線上索引作業時要特別小心。Because an S-lock or Sch-M lock is held in the final phase of the index operation, be careful when you run an online index operation inside an explicit user transaction, such as BEGIN TRANSACTION...COMMIT block. 這樣做導致交易完後才執行鎖定,而妨礙使用者進行並行作業。Doing this causes the lock to be held until the end of the transaction, therefore impeding user concurrency.

當線上索引重建可搭配 MAX DOP > 1ALLOW_PAGE_LOCKS = OFF 選項執行時,可能會增加片段。Online index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF options. 如需詳細資訊,請參閱 運作方式:線上索引重建 - 可能會導致片段增加For more information, see How It Works: Online Index Rebuild - Can Cause Increased Fragmentation.

交易記錄考量因素Transaction log considerations

大規模的索引作業,無論是離線或線上執行,都會產生大量資料負載,而很快就填滿了交易記錄。Large-scale index operations, performed offline or online, can generate large data loads that can cause the transaction log to quickly fill. 若要確定可以回復索引作業,在索引作業完成以前,不能截斷交易記錄;不過,在索引作業期間可以備份此記錄。To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has been completed; however, the log can be backed up during the index operation. 因此,在索引作業期間,交易記錄必須有足夠的空間,才能儲存索引作業交易與任何並行使用者交易。Therefore, the transaction log must have sufficient space to store both the index operation transactions and any concurrent user transactions for the duration of the index operation. 如需詳細資訊,請參閱 索引作業的交易記錄磁碟空間For more information, see Transaction Log Disk Space for Index Operations.

可繼續索引考量因素Resumable index considerations

注意

建立索引和索引重建的可繼續索引選項適用於 SQL Server (索引重建從 SQL Server 2017 開始,且 SQL Server 2019 也支援建立索引) 和 SQL Database。The resumable index option for create index and index rebuild applies to SQL Server (index rebuild starting with SQL Server 2017, with create index also supported in SQL Server 2019) and SQL Database. 請參閱 Create IndexAlter IndexSee Create Index and Alter Index.

當您執行可繼續的線上索引建立或重建時,將適用下列指導方針:When you perform resumable online index create or rebuild, the following guidelines apply:

  • 管理、規劃和擴充索引的維護期間。Managing, planning and extending of index maintenance windows. 您可以暫停和重新啟動索引建立或重建作業多次,以符合您的維護期間。You can pause and restart an index create or rebuild operation multiple times to fit your maintenance windows.
  • 從索引建立或重建失敗 (例如資料庫容錯移轉或磁碟空間不足) 中復原。Recovering from index create or rebuild failures (such as database failovers or running out of disk space).
  • 當索引作業暫停時,原始索引和新建立的索引都需要磁碟空間,而且必須在 DML 作業期間進行更新。When an index operation is paused, both the original index and the newly created one require disk space and need to be updated during DML operations.
  • 在索引建立或重建作業時,啟用交易記錄的截斷功能。Enables truncation of transaction logs during an index create or rebuild operation.
  • 不支援 SORT_IN_TEMPDB=ON 選項SORT_IN_TEMPDB=ON option is not supported

重要

可繼續的索引建立或重建不需要您持續開啟長時間執行的交易,允許在此作業期間執行記錄截斷,而讓記錄空間管理的效能更佳。Resumable index create or rebuild does not require you to keep open a long running transaction, allowing log truncation during this operation and a better log space management. 利用新的設計,我們設法將資料庫中的必要資料與重新啟動可繼續作業所需的所有參考保存在一起。With the new design, we managed to keep necessary data in a database together with all references required to restart the resumable operation.

一般而言,可繼續和不可繼續的線上索引重建之間沒有效能差異。Generally, there is no performance difference between resumable and non-resumable online index rebuild. 對於建立可繼續的索引,會有一定的額外負荷造成可繼續和不可繼續的索引建立之間出現細微的效能差異。For create resumable index, there is a constant overhead that causes a small performance difference between resumable and non-resumable index create. 這種差異只有在較小的資料表上才非常明顯。This difference is mostly noticeable only for smaller tables.

若在索引作業暫停時,更新可繼續的索引:When you update a resumable index while an index operation is paused:

  • 對於最常讀取的工作負載,效能影響微不足道。For read-mostly workloads, the performance impact is insignificant.
  • 對於更新繁重的工作負載,您可能會遇到輸送量降低情況 (我們的測試顯示小於 10% 的降低情況)。For update-heavy workloads, you may experience some throughput degradation (our testing shows less than 10% degradation).

一般而言,可繼續和不可繼續的線上索引建立或重建之間的磁碟重組品質沒有差異。Generally, there is no difference in defragmentation quality between resumable and non-resumable online index create or rebuild.

線上預設選項Online default options

重要

這些選項處於 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 的公開預覽階段。These options are in public preview for SQL Server 2019 (15.x)SQL Server 2019 (15.x).

您可以透過設定 ELEVATE_ONLINE 或 ELEVATE_RESUMABLE 資料庫範圍設定選項,在資料庫層級設定線上或可繼續的預設選項。You can set default options for online or resumable at a database level by setting the ELEVATE_ONLINE or ELEVATE_RESUMABLE database scoped configuration options. 使用這些預設選項,您可以避免不小心執行讓資料庫資料表離線的作業。With these default options, you can avoid accidentally performing an operation that takes your database table offline. 這兩個選項都會使引擎自動將某些作業提升為線上或可繼續執行。Both options will cause the engine to automatically elevate certain operations to online or resumable execution.
您可以使用 ALTER DATABASE SCOPED CONFIGURATION 命令,將選項設為 FAIL_UNSUPPORTED、WHEN_SUPPORTED 或 OFF。You can set either option as FAIL_UNSUPPORTED, WHEN_SUPPORTED, or OFF using the ALTER DATABASE SCOPED CONFIGURATION command. 您可以為線上和可繼續設定不同的值。You can set different values for online and resumable.

ELEVATE_ONLINE 和 ELEVATE_RESUMABLE 僅適用於分別支援線上和可繼續語法的 DDL 陳述式。Both ELEVATE_ONLINE and ELEVATE_RESUMABLE only apply to DDL statements that support the online and resumable syntax respectively. 例如,如果您嘗試建立 ELEVATE_ONLINE=FAIL_UNSUPORTED 的 XML 索引,因為 XML 索引不支援 ONLINE= 語法,所以會離線執行作業。For example, if you attempt to create an XML index with ELEVATE_ONLINE=FAIL_UNSUPORTED, the operation will run offline since XML indexes don't support the ONLINE= syntax. 該選項只會影響未指定 ONLINE 或 RESUMABLE 選項所送出的 DDL 陳述式。The options only effect DDL statements that are submitted without specifying an ONLINE or RESUMABLE option. 例如,藉由送出 ONLINE=OFF 或 RESUMABLE=OFF 的陳述式,使用者可以覆寫 FAIL_UNSUPPORTED 設定,並離線及/或以不可繼續的方式執行陳述式。For example, by submitting a statement with ONLINE=OFF or RESUMABLE=OFF, the user can override a FAIL_UNSUPPORTED setting and run a statement offline and/or non-resumably.

注意

ELEVATE_ONLINE 和 ELEVATE_RESUMABLE 不適用於 XML 索引作業。ELEVATE_ONLINE and ELEVATE_RESUMABLE does not apply to XML index operations.