在 Azure SQL 資料庫中設定平行處理原則的最大程度 (MAXDOP)

  • 發行項

適用於：Azure SQL 資料庫

本文描述 Azure SQL 資料庫中平行處理原則的最大程度 (MAXDOP) 設定。

注意

本內容著重於 Azure SQL 資料庫。 Azure SQL 資料庫是以最新穩定版本的 Microsoft SQL Server 資料庫引擎為基礎，因此多數內容相似，但是疑難排解和設定選項可能有所不同。 如需有關 SQL Server 中 MAXDOP 的更多資訊，請參閱設定平行處理原則的最大程度伺服器組態選項。

概觀

MAXDOP 會控制資料庫引擎中的內部查詢平行處理。 較高的 MAXDOP 值通常會導致每個查詢有更多平行執行緒，並加快查詢執行的速度。

在 Azure SQL 資料庫中，每個新的單一資料庫和彈性集區資料庫，預設 MAXDOP 設定為 8。 此預設值可防止不必要的資源使用率，同時仍允許資料庫引擎使用平行執行緒以更快執行查詢。 通常不需要進一步設定 Azure SQL 資料庫工作負載中的 MAXDOP，雖然可能有助於做為先進效能微調的練習。

注意

在 2020 年 9 月，根據 Azure SQL 資料庫服務中數年的遙測，新資料庫的預設值 MAXDOP 8 是最適合各種客戶工作負載的最佳值。 此預設值有助於防止因為過度的平行處理原則而發生效能問題。 在此之前，新資料庫的預設設定為 MAXDOP 0。 在 2020 年 9 月之前建立的現有資料庫不會自動變更 MAXDOP。

一般情況下，如果資料庫引擎選擇使用平行處理原則來執行查詢，執行速度會較快。 但過度的平行處理原則可能會耗用額外的處理器資源，且查詢效能並不會改善。 某種程度上，過度的平行處理原則可能會對所有在相同資料庫引擎執行個體上執行的查詢產生查詢效能的負面影響。 傳統上，設定平行處理原則的上限是 SQL Server 工作負載中常見的效能微調練習。

下表說明使用不同 MAXDOP 值執行查詢時的資料庫引擎行為：

MAXDOP 行為
= 1 資料庫引擎會使用單一序列執行緒來執行查詢。 不會使用平行執行緒。
> 1 資料庫引擎會將平行執行緒所使用的其他排程器數目設定為 MAXDOP 值，或是邏輯核心的總數目，以較小者為準。
= 0 資料庫引擎會將平行執行緒所使用的其他排程器數目設定為邏輯核心的總數目或 64，以較小者為準。

注意

每個查詢都會以至少一個排程器執行，並在該排程上器執行一個背景工作執行緒。

以平行處理原則執行的查詢會使用其他排程器和額外的平行執行緒。 因為多個平行執行緒可能會在相同的排程器上執行，所以用來執行查詢的執行緒總數可能會高於指定的 MAXDOP 值或邏輯處理器的總數目。 如需詳細資訊，請參閱排程平行工作。

考量

  • 在 Azure SQL 資料庫中，您可以變更預設的 MAXDOP 值：

  • 長期 SQL Server MAXDOP 考慮和建議適用於 Azure SQL 資料庫。

  • 建立或重建索引的索引作業，或者卸除叢集索引的索引作業，都需要大量資源。 您可以在 CREATE INDEX 或 ALTER INDEX 陳述式中指定 MAXDOP 索引選項，藉以覆寫索引作業的資料庫 MAXDOP 值。 MAXDOP 值會在執行時套用至陳述式，且不會儲存在索引中繼資料內。 如需詳細資訊，請參閱 設定平行索引作業。

  • 除了查詢和編製索引作業之外，MAXDOP 的資料庫範圍設定選項也會控制可能使用平行執行的其他陳述式平行處理，例如 DBCC CHECKTABLE、DBCC CHECKDB 和 DBCC CHECKFILEGROUP。

建議

變更資料庫的 MAXDOP 可能會對查詢效能和資源使用率造成正面和負面的重大影響。 不過，並沒有適合所有工作負載的單一 MAXDOP 值。 設定 MAXDOP 的建議有細微差異，並取決於許多因素。

某些尖峰同時工作負載可能在特定 MAXDOP 設定下運作更良好。 適當設定的 MAXDOP 應該會降低效能和可用性事件的風險，而在某些情況下，可能會因避免不必要的資源使用率而降低成本，進而縮減至較低的服務目標。

過度的平行處理

較高的 MAXDOP 通常會減少需要大量 CPU 的查詢持續時間。 然而，過度的平行處理原則可能會佔用其他查詢的 CPU 和背景工作執行緒資源，進一步導致其他同時工作負載效能惡化。 在極端情況下，過度的平行處理原則可能會耗用所有資料庫或彈性集區資源，因而造成查詢逾時、錯誤和應用程式中斷。

提示

我們建議客戶避免將 MAXDOP 設定為 0，即使目前看似沒有造成問題。

當同時要求數目超過服務目標所提供的 CPU 和工作者執行緒資源所能支援的同時要求時，過度的平行處理會成為最大的問題。 請避免 MAXDOP 0，以降低在資料庫擴大時，或是 Azure SQL 資料庫的未來硬體設定為相同的資料庫服務目標提供更多核心時，平行處理原則所造成潛在未來問題的風險。

修改 MAXDOP

如果您判斷與非預設的 MAXDOP 值更適合您的 Azure SQL 資料庫工作負載，則可以使用 ALTER DATABASE SCOPED CONFIGURATION T SQL 陳述式。 如需範例，請參閱下面的使用 Transact-SQL 的範例一節。 若要針對您所建立的每個新資料庫，將 MAXDOP 變更為非預設值，請將此步驟新增至您的資料庫部署程序。

如果非預設 MAXDOP 僅適用於工作負載中的一小部分查詢，您可以在查詢層級上覆寫 MAXDOP，方法是新增 OPTION (MAXDOP) 提示。 如需範例，請參閱下面的使用 Transact-SQL 的範例一節。

使用負載測試來徹底測試您的 MAXDOP 設定變更，包括實際的同時查詢負載。

如果有不同的 MAXDOP 設定適用於您的讀寫和唯讀工作負載，則可以獨立設定主要和次要複本的 MAXDOP。 這適用於 Azure SQL 資料庫讀取縮放、異地複寫和超大規模次要複本。 依預設，所有次要複本都會繼承主要複本的 MAXDOP 設定。

安全性

權限

ALTER DATABASE SCOPED CONFIGURATION 陳述式必須以伺服器管理員、資料庫角色 db_owner 的成員，或已被授與 ALTER ANY DATABASE SCOPED CONFIGURATION 權限的使用者身分執行。

範例

當針對 Azure SQL 資料庫的新單一資料庫選擇 AdventureWorksLT 選項時，這些範例會使用最新的 SAMPLE 資料庫範例。

PowerShell

MAXDOP 資料庫範圍設定

此範例示範如何使用 ALTER DATABASE SCOPED CONFIGURATION 陳述式將 MAXDOP 設定設為 2。 此設定會立即套用至新的查詢。 PowerShell Cmdlet Invoke-SqlCmd 會執行要設定的 T-SQL 查詢，並傳回 MAXDOP 資料庫範圍設定。

$dbName = "sample" 
$serverName = <server name here>
$serveradminLogin = <login here>
$serveradminPassword = <password here>
$desiredMAXDOP = 8

$params = @{
    'database' = $dbName
    'serverInstance' =  $serverName
    'username' = $serveradminLogin
    'password' = $serveradminPassword
    'outputSqlErrors' = $true
    'query' = 'ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = ' + $desiredMAXDOP + ';
     SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''MAXDOP'';'
  }
  Invoke-SqlCmd @params

此範例適用於已啟用讀取縮放複本、異地複寫和 Azure SQL 資料庫超大規模次要複本的 Azure SQL 資料庫。 例如，主要複本會設定為與次要複本不同的預設 MAXDOP，並預期讀寫和唯讀工作負載之間可能會有差異。

$dbName = "sample" 
$serverName = <server name here>
$serveradminLogin = <login here>
$serveradminPassword = <password here>
$desiredMAXDOP_primary = 8
$desiredMAXDOP_secondary_readonly = 1
 
$params = @{
    'database' = $dbName
    'serverInstance' =  $serverName
    'username' = $serveradminLogin
    'password' = $serveradminPassword
    'outputSqlErrors' = $true
    'query' = 'ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = ' + $desiredMAXDOP_primary + ';
    ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = ' + $desiredMAXDOP_secondary_readonly + ';
    SELECT [value], value_for_secondary FROM sys.database_scoped_configurations WHERE [name] = ''MAXDOP'';'
  }
  Invoke-SqlCmd @params

Transact-SQL

您可以使用 Azure 入口網站查詢編輯器、SQL Server Management Studio (SSMS) 或 Azure Data Studio，針對您的 Azure SQL 資料庫查詢執行 T-SQL。

  1. 開啟新的查詢視窗。

  2. 連線至您想要變更 MAXDOP 的資料庫。 您無法變更 master 資料庫中的資料庫範圍設定。

  3. 複製下列範例並將其貼到查詢視窗中，然後選取 [執行]。

MAXDOP 資料庫範圍設定

此範例說明如何使用 sys.database_scoped_configurations 系統目錄檢視，判斷目前資料庫的 MAXDOP 資料庫範圍設定。

SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';

此範例示範如何使用 ALTER DATABASE SCOPED CONFIGURATION 陳述式將 MAXDOP 設定設為 8。 此設定會立即生效。

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

此範例適用於已啟用讀取縮放複本、異地複寫和超大規模次要複本的 Azure SQL 資料庫。 例如，主要複本會設定為與次要複本不同的 MAXDOP，並預期讀寫和唯讀工作負載之間可能會有差異。 所有陳述式都會在主要複本上執行。 sys.database_scoped_configurations 的 value_for_secondary 資料行包含次要複本的設定。

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 1;
SELECT [value], value_for_secondary FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';

MAXDOP 查詢提示

此範例示範如何使用查詢提示來執行查詢，以強制 max degree of parallelism 設為 2。

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM SalesLT.SalesOrderDetail  
WHERE UnitPrice < 5  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2);    
GO

MAXDOP 索引選項

此範例示範如何使用索引選項來重建索引，以強制 max degree of parallelism 設為 12。

ALTER INDEX ALL ON SalesLT.SalesOrderDetail 
REBUILD WITH 
   (     MAXDOP = 12
       , SORT_IN_TEMPDB = ON
       , ONLINE = ON);

另請參閱

後續步驟