設定平行索引作業Configure Parallel Index Operations

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主題定義平行處理原則的最大程度,並說明如何在 SQL Server 2017SQL Server 2017 中使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL修改此設定。This topic defines max degree of parallelism and explains how to modify this setting in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

在執行 SQL ServerSQL Server Enterprise 或更新版本的多處理器系統上,索引陳述式可能會如同其他查詢般,使用多個處理器 (CPU) 來執行與索引陳述式相關聯的掃描、排序和索引作業。On multiprocessor systems that are running SQL ServerSQL Server Enterprise or higher, index statements may use multiple processors (CPUs) to perform the scan, sort, and index operations associated with the index statement just like other queries do. 執行單一索引陳述式所用的 CPU 數目是由平行處理原則的最大程度伺服器組態選項、目前的工作負載以及索引統計資料所決定的。The number of CPUs used to run a single index statement is determined by the max degree of parallelism server configuration option, the current workload, and the index statistics. max degree of parallelism 選項會決定用於執行平行計畫的最大處理器數目。The max degree of parallelism option determines the maximum number of processors to use in parallel plan execution. 如果 SQL Server Database EngineSQL Server Database Engine 偵測到系統忙碌中,在陳述式執行開始之前,會先自動降低索引作業之平行處理原則的程度。If the SQL Server Database EngineSQL Server Database Engine detects that the system is busy, the degree of parallelism of the index operation is automatically reduced before statement execution starts. 如果非資料分割索引的前端索引鍵資料行具有有限的相異值數目,或者每個相異值的頻率具有大幅差異, Database EngineDatabase Engine 也可能會降低平行處理原則的程度。The Database EngineDatabase Engine can also reduce the degree of parallelism if the leading key column of a non-partitioned index has a limited number of distinct values or the frequency of each distinct value varies significantly. 如需詳細資訊,請參閱查詢處理架構指南For more information, see Query Processing Architecture Guide.

注意

並非所有 SQL ServerSQL Server 版本都可使用平行索引作業。Parallel index operations are not available in every SQL ServerSQL Server edition. 如需詳細資訊,請參閱 SQL Server 2016 版本支援的功能For more information, see Features Supported by the Editions of SQL Server 2016.

本主題內容In This Topic

開始之前Before You Begin

限制事項Limitations and Restrictions

  • 查詢最佳化工具所使用的處理器數目通常可以提供最佳的效能。The number of processors that are used by the query optimizer typically provides optimal performance. 然而,諸如建立、重建、卸除非常大的索引都需要大量的資源,並可能在索引作業期間造成其他應用程式和資料庫作業的資源不足。However, operations such as creating, rebuilding, or dropping very large indexes are resource intensive and can cause insufficient resources for other applications and database operations for the duration of the index operation. 當發生此問題時,您可以限制索引作業要使用的處理器數目,藉以手動設定執行索引陳述式要使用的最大處理器數目。When this problem occurs, you can manually configure the maximum number of processors that are used to run the index statement by limiting the number of processors to use for the index operation.

  • MAXDOP 索引選項只會針對指定此選項的查詢來覆寫 max degree of parallelism 組態選項。The MAXDOP index option overrides the max degree of parallelism configuration option only for the query specifying this option. 下表列出可以使用 max degree of parallelism 組態選項及 MAXDOP 索引選項指定的有效整數值。The following table lists the valid integer values that can be specified with the max degree of parallelism configuration option and the MAXDOP index option.

    ReplTest1Value DescriptionDescription
    00 指定伺服器會根據目前的系統工作負載來決定所使用的 CPU 數目。Specifies that the server determines the number of CPUs that are used, depending on the current system workload. 這是預設值且為建議的設定。This is the default value and recommended setting.
    11 隱藏平行計畫的產生。Suppresses parallel plan generation. 作業必須循序執行。The operation will be executed serially.
    2-642-64 將處理器的數目限制成指定的值。Limits the number of processors to the specified value. 視目前的工作負載而定來使用較少的處理器。Fewer processors may be used depending on the current workload. 如果指定的值大於可用的 CPU 個數,就會使用實際可用的 CPU 個數。If a value larger than the number of available CPUs is specified, the actual number of available CPUs is used.
  • 平行索引執行與 MAXDOP 索引選項適用於下列 Transact-SQLTransact-SQL 陳述式:Parallel index execution and the MAXDOP index option apply to the following Transact-SQLTransact-SQL statements:

  • ALTER INDEX (...) REORGANIZE 陳述式中無法指定 MAXDOP 索引選項。The MAXDOP index option cannot be specified in the ALTER INDEX (...) REORGANIZE statement.

  • 如果查詢最佳化工具將平行處理原則的程度套用至建立作業,則需要排序的資料分割索引作業可能需要更多的記憶體。Memory requirements for partitioned index operations that require sorting can be greater if the Query Optimizer applies degrees of parallelism to the build operation. 平行處理原則的程度愈高,所需的記憶體就愈大。The higher the degrees of parallelism, the greater the memory requirement is. 如需詳細資訊,請參閱< Partitioned Tables and Indexes>。For more information, see Partitioned Tables and Indexes.

權限 Permissions

必須具備資料表或檢視的 ALTER 權限。Requires ALTER permission on the table or view.

使用 SQL Server Management StudioUsing SQL Server Management Studio

若要在索引上設定平行處理原則的最大程度To set max degree of parallelism on an index

  1. 在 [物件總管] 中,按一下加號展開資料庫,此資料庫包含您要設定索引之平行處理原則最大程度的資料表。In Object Explorer, click the plus sign to expand the database that contains the table on which you want to set max degree of parallelism for an index.

  2. 展開 [資料表] 資料夾。Expand the Tables folder.

  3. 按一下加號展開包含您要設定索引之平行處理原則最大程度的資料表。Click the plus sign to expand the table on which you want to set max degree of parallelism for an index.

  4. 展開 [索引] 資料夾。Expand the Indexes folder.

  5. 以滑鼠右鍵按一下要設定平行處理原則最大程度的索引,然後選取 [屬性] 。Right-click the index for which you want to set the max degree of parallelism and select Properties.

  6. [選取頁面] 底下,選取 [選項]Under Select a page, select Options.

  7. 選取 [平行處理原則的最大程度] ,然後輸入介於 1 和 64 之間的一些值。Select Maximum degree of parallelism, and then enter some value between 1 and 64.

  8. 按一下 [確定] 。Click OK.

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

使用 Transact-SQLUsing Transact-SQL

若要在現有索引上設定平行處理原則的最大程度To set max degree of parallelism on an existing index

  1. [物件總管] 中,連接到 Database EngineDatabase Engine的執行個體。In Object Explorer, connect to an instance of Database EngineDatabase Engine.

  2. 在標準列上,按一下 [新增查詢]On the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;   
    GO  
    /*Alters the IX_ProductVendor_VendorID index on the Purchasing.ProductVendor table so that, if the server has eight or more processors, the Database Engine will limit the execution of the index operation to eight or fewer processors.  
    */  
    ALTER INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor  
    REBUILD WITH (MAXDOP=8);   
    GO  
    

如需詳細資訊,請參閱 ALTER INDEX (Transact-SQL)For more information, see ALTER INDEX (Transact-SQL).

在新索引上設定平行處理原則的最大程度Set max degree of parallelism on a new index

  1. [物件總管] 中,連接到 Database EngineDatabase Engine的執行個體。In Object Explorer, connect to an instance of Database EngineDatabase Engine.

  2. 在標準列上,按一下 [新增查詢]On the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    CREATE INDEX IX_ProductVendor_NewVendorID   
    ON Purchasing.ProductVendor (BusinessEntityID)  
    WITH (MAXDOP=8);  
    GO  
    

另請參閱See also

查詢處理架構指南 Query Processing Architecture Guide
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
DROP INDEX (Transact-SQL) DROP INDEX (Transact-SQL)
ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
ALTER TABLE table_constraint (Transact-SQL) ALTER TABLE table_constraint (Transact-SQL)
ALTER TABLE index_option (Transact-SQL)ALTER TABLE index_option (Transact-SQL)