ALTER WORKLOAD GROUP (Transact-SQL)

發行項
05/04/2023

選取產品

在以下資料列中，選取您感興趣的產品名稱，隨即只會顯示該產品的資訊。

* SQL Server *

SQL 受控執行個體

Azure Synapse
分析

SQL Server 與 SQL 受控執行個體

變更現有的資源管理員工作負載群組設定，並選擇性地將其指派給資源管理員資源集區。

注意

針對Azure SQL 受控執行個體，您必須位於資料庫的內容 master 中，才能改變 Resource Governor 組態。

Transact-SQL 語法慣例。

語法

ALTER WORKLOAD GROUP { group_name | "default" }
[ WITH
    ([ IMPORTANCE = { LOW | MEDIUM | HIGH } ]
      [ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT = value ]
      [ [ , ] REQUEST_MAX_CPU_TIME_SEC = value ]
      [ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value ]
      [ [ , ] MAX_DOP = value ]
      [ [ , ] GROUP_MAX_REQUESTS = value ] )
]
[ USING { pool_name | "default" } ]
[ ; ]

引數

group_name | "default"

現有使用者定義的工作負載群組名稱，或 Resource Governor 預設工作負載群組的名稱。在安裝 SQL Server 時，Resource Governor 就會建立此「預設」和內部群組。

與 ALTER WORKLOAD GROUP 搭配使用時，"default" 選項必須加上引號 ("") 或方括號 ([])，才能避免與系統保留字 DEFAULT 產生衝突。如需詳細資訊，請參閱＜ Database Identifiers＞。

預先定義的工作負載群組和資源集區都會使用小寫名稱，例如 "default"。如果是使用區分大小寫之定序的伺服器，則應該將此列入考量。使用不區分大小寫定序的伺服器 (例如 SQL_Latin1_General_CP1_CI_AS) 會將 "default" 與 "Default" 視為相同。

IMPORTANCE = { LOW | MEDIUM | HIGH }

指定要求在工作負載群組中的相對重要性。重要性為下列其中一項：

LOW
MEDIUM (預設值)
HIGH

在內部，每個重要性設定都會儲存為計算所使用的數字。

IMPORTANCE 對資源集區而言是本機的；相同資源集區內部不同重要性的工作負載群組會彼此影響，但不會影響另一個資源集區中的工作負載群組。

REQUEST_MAX_MEMORY_GRANT_PERCENT = value

指定單一要求可由集區中獲取的記憶體最大數量。 value 是相對於 MAX_MEMORY_PERCENT 所指定的資源集區大小的百分比。預設值為 25。指定的數量僅參考查詢執行授與記憶體。

在 SQL Server 2017 (14.x) 之前，value 是一個 int，允許的範圍為 1 到 100。從 SQL Server 2019 (15.x) 開始，值為 float 資料類型，允許的範圍為 0 到 100。

重要

將 value 設定為 0 會避免在使用者定義的工作負載群組中，執行具有 SORT 和 HASH JOIN 作業的查詢。

不建議您將 value 設定為大於 70，因為如果其他並行查詢正在執行，伺服器可能無法將足夠的記憶體擱置在一旁。最後，這可能會導致查詢逾時錯誤 8645。

如果查詢記憶體需求超過這個參數所指定的限制，伺服器會執行以下作業：

如果是使用者定義的工作負載群組，伺服器會嘗試減少查詢的平行處理原則程度，直到記憶體需求低於此限制，或是直到平行處理原則程度等於 1 為止。如果查詢記憶體需求仍然大於此限制，將會發生錯誤 8657。
如果是內部和預設的工作負載群組，伺服器會允許查詢取得所需的記憶體。

如果伺服器沒有足夠的實體記憶體，這兩種情況都會受限於逾時錯誤 8645。

REQUEST_MAX_CPU_TIME_SEC = value

指定要求可以使用的最大 CPU 時間量 (以秒為單位)。 value 必須是 0 或正整數。 value 的預設設定為 0，這代表沒有限制。根據預設，Resource Governor 不會在超過時間上限時阻止要求繼續執行。不過，系統將會產生某個事件。如需詳細資訊，請參閱超過 CPU 閾值事件類別。

從 SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x) CU3 開始，使用追蹤旗標 2422 時，Resource Governor 將會在超過時間上限時中止要求。

REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value

指定查詢能夠等待記憶體授權 (工作緩衝區記憶體) 變成可用的最大時間 (以秒為單位)。

到達記憶體授與逾時值時，查詢不一定會失敗。只有當有太多並行的查詢正在執行時，查詢才會失敗。否則，查詢可能只會得到最小的記憶體授權，導致查詢效能降低。

value 必須為正整數。 value 的預設設定 0 會根據查詢成本使用內部計算來判斷最大時間。

MAX_DOP = value

為平行要求指定平行處理原則的最大程度 (DOP)。 value 必須是 0 或正整數 (1 到 255)。當 value 為 0 時，伺服器會選擇平行處理原則的最大程度。這是預設值且為建議的設定。

資料庫引擎為 MAX_DOP 設定的實際值可能小於指定的值。最終的值是由公式 min(255, CPU 的數目) 所決定。

警告

變更 MAX_DOP 可能會對伺服器的效能造成不良影響。如果您必須變更 MAX_DOP，我們建議您將它設定為小於或等於存在單一 NUMA 節點中之最大硬體排程器數目的值。我們建議您不要將 MAX_DOP 設定為大於 8 的值。

MAX_DOP 會以下列方式處理：

作為查詢提示的 MAX_DOP 只要未超過工作負載群組 MAX_DOP 即有效。
當做查詢提示的 MAX_DOP 永遠會覆寫 sp_configure 的「平行處理原則的最大程度」。
工作負載群組 MAX_DOP 會覆寫 sp_configure 的「平行處理原則的最大程度」。
如果查詢在編譯時間被標示為序列 (MAX_DOP = 1)，不管工作負載群組或 sp_configure 設定為何，都無法在執行階段將該查詢變更回平行。

DOP 經過設定後，在授與記憶體不足的壓力下，僅能將其降低。在授與記憶體佇列中等候時，看不到工作負載群組的重新設定。

GROUP_MAX_REQUESTS = value

指定在工作負載群組中可允許執行的最大同時要求數。 value 必須是 0 或正整數。 value 的預設設定 0 允許無限制的要求。達到並行要求數上限時，該群組中的使用者可以登入，但是會處於等候狀態，直到並行要求數低於指定的值。

USING { pool_name | "default" }

將工作負載群組與 pool_name 所識別的使用者定義資源集區產生關聯，實際上會將工作負載群組放入資源集區中。若未提供 pool_name 或未使用 USING 引數，工作負載群組會放入預先定義的 Resource Governor 預設集區中。

與 ALTER WORKLOAD GROUP 搭配使用時，"default" 選項必須加上引號 ("") 或方括號 ([[]])，以避免與系統保留字 DEFAULT 產生衝突。如需詳細資訊，請參閱＜ Database Identifiers＞。

備註

允許對預設群組使用 ALTER WORKLOAD GROUP。

在 ALTER RESOURCE GOVERNOR RECONFIGURE 執行後，工作負載群組設定的變更才會生效。如果變更計劃會影響到設定，只有在執行 DBCC FREEPROCCACHE (*pool_name*) 之後，新的設定才會在先前已快取的計劃中生效，其中，pool_name 是工作負載群組相關聯之 Resource Governor 資源集區的名稱。

如果將 MAX_DOP 變更為 1，則不需要執行 DBCC FREEPROCCACHE，因為平行計劃可以在序列模式中執行。不過，它可能不會和編譯為序列計劃的計劃一樣有效率。
如果將 MAX_DOP 從 1 變更為 0 或大於 1 的值，則不需要執行 DBCC FREEPROCCACHE。不過，序列計劃無法以平行方式執行，因此，清除個別的快取將會使新計劃有可能使用平行處理原則進行編譯。

警告

從與超過一個工作負載群組相關聯的資源集區清除已快取的計劃，會影響包含以 pool_name 所識別使用者定義資源集區的所有工作負載群組。

執行 DDL 陳述式時，您應熟悉 Resource Governor 狀態。如需詳細資訊，請參閱 Resource Governor。

REQUEST_MEMORY_GRANT_PERCENT：在 SQL Server 2005 (9.x) 中，允許在建立索引時使用比最初授與的還要多的工作區記憶體來改善效能。資源管理員的更新版本中支援此特殊處理，不過，初始授與和任何額外的記憶體授與都會受到資源集區和工作負載群組設定的限制。

在分割資料表上建立索引

非對齊式分割區資料表上之索引建立所耗用的記憶體，與相關的分割區數目成正比。如果所需的總記憶體超出 Resource Governor 工作負載群組設定所施加的每一查詢限制 (REQUEST_MAX_MEMORY_GRANT_PERCENT)，這個索引建立動作就可能無法執行。為了與 SQL Server 2005 (9.x) 相容，「預設」工作負載群組允許查詢在使用啟動所需的最低記憶體時超過每個查詢的限制，因此使用者或許能夠在「預設」工作負載群組中執行相同的索引建立作業，但前提是「預設」資源集區有設定足夠的總記憶體來執行這類查詢。

權限

需要 CONTROL SERVER 權限。

範例

下列範例顯示如何將預設群組中的要求重要性從 MEDIUM 變更為 LOW。

ALTER WORKLOAD GROUP "default"
WITH (IMPORTANCE = LOW);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

下列範例說明如何將工作負載群組從其所屬集區移至預設集區。

ALTER WORKLOAD GROUP adHoc
USING [default];
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

另請參閱

SQL Server

* SQL 受控執行個體 *

Azure Synapse
分析

SQL Server 與 SQL 受控執行個體

變更現有的資源管理員工作負載群組設定，並選擇性地將其指派給資源管理員資源集區。

注意

針對Azure SQL 受控執行個體，您必須位於資料庫的內容 master 中，才能改變 Resource Governor 組態。

Transact-SQL 語法慣例。

語法

ALTER WORKLOAD GROUP { group_name | "default" }
[ WITH
    ([ IMPORTANCE = { LOW | MEDIUM | HIGH } ]
      [ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT = value ]
      [ [ , ] REQUEST_MAX_CPU_TIME_SEC = value ]
      [ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value ]
      [ [ , ] MAX_DOP = value ]
      [ [ , ] GROUP_MAX_REQUESTS = value ] )
]
[ USING { pool_name | "default" } ]
[ ; ]

引數

group_name | "default"

現有使用者定義的工作負載群組名稱，或 Resource Governor 預設工作負載群組的名稱。在安裝 SQL Server 時，Resource Governor 就會建立此「預設」和內部群組。

IMPORTANCE = { LOW | MEDIUM | HIGH }

指定要求在工作負載群組中的相對重要性。重要性為下列其中一項：

LOW
MEDIUM (預設值)
HIGH

在內部，每個重要性設定都會儲存為計算所使用的數字。

IMPORTANCE 對資源集區而言是本機的；相同資源集區內部不同重要性的工作負載群組會彼此影響，但不會影響另一個資源集區中的工作負載群組。

REQUEST_MAX_MEMORY_GRANT_PERCENT = value

在 SQL Server 2017 (14.x) 之前，value 是一個 int，允許的範圍為 1 到 100。從 SQL Server 2019 (15.x) 開始，值為 float 資料類型，允許的範圍為 0 到 100。

重要

將 value 設定為 0 會避免在使用者定義的工作負載群組中，執行具有 SORT 和 HASH JOIN 作業的查詢。

如果查詢記憶體需求超過這個參數所指定的限制，伺服器會執行以下作業：

如果是使用者定義的工作負載群組，伺服器會嘗試減少查詢的平行處理原則程度，直到記憶體需求低於此限制，或是直到平行處理原則程度等於 1 為止。如果查詢記憶體需求仍然大於此限制，將會發生錯誤 8657。
如果是內部和預設的工作負載群組，伺服器會允許查詢取得所需的記憶體。

如果伺服器沒有足夠的實體記憶體，這兩種情況都會受限於逾時錯誤 8645。

REQUEST_MAX_CPU_TIME_SEC = value

從 SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x) CU3 開始，使用追蹤旗標 2422 時，Resource Governor 將會在超過時間上限時中止要求。

REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value

指定查詢能夠等待記憶體授權 (工作緩衝區記憶體) 變成可用的最大時間 (以秒為單位)。

value 必須為正整數。 value 的預設設定 0 會根據查詢成本使用內部計算來判斷最大時間。

MAX_DOP = value

資料庫引擎為 MAX_DOP 設定的實際值可能小於指定的值。最終的值是由公式 min(255, CPU 的數目) 所決定。

警告

MAX_DOP 會以下列方式處理：

作為查詢提示的 MAX_DOP 只要未超過工作負載群組 MAX_DOP 即有效。
當做查詢提示的 MAX_DOP 永遠會覆寫 sp_configure 的「平行處理原則的最大程度」。
工作負載群組 MAX_DOP 會覆寫 sp_configure 的「平行處理原則的最大程度」。
如果查詢在編譯時間被標示為序列 (MAX_DOP = 1)，不管工作負載群組或 sp_configure 設定為何，都無法在執行階段將該查詢變更回平行。

DOP 經過設定後，在授與記憶體不足的壓力下，僅能將其降低。在授與記憶體佇列中等候時，看不到工作負載群組的重新設定。

GROUP_MAX_REQUESTS = value

USING { pool_name | "default" }

備註

允許對預設群組使用 ALTER WORKLOAD GROUP。

如果將 MAX_DOP 變更為 1，則不需要執行 DBCC FREEPROCCACHE，因為平行計劃可以在序列模式中執行。不過，它可能不會和編譯為序列計劃的計劃一樣有效率。
如果將 MAX_DOP 從 1 變更為 0 或大於 1 的值，則不需要執行 DBCC FREEPROCCACHE。不過，序列計劃無法以平行方式執行，因此，清除個別的快取將會使新計劃有可能使用平行處理原則進行編譯。

警告

從與超過一個工作負載群組相關聯的資源集區清除已快取的計劃，會影響包含以 pool_name 所識別使用者定義資源集區的所有工作負載群組。

執行 DDL 陳述式時，您應熟悉 Resource Governor 狀態。如需詳細資訊，請參閱 Resource Governor。

在分割資料表上建立索引

權限

需要 CONTROL SERVER 權限。

範例

下列範例顯示如何將預設群組中的要求重要性從 MEDIUM 變更為 LOW。

ALTER WORKLOAD GROUP "default"
WITH (IMPORTANCE = LOW);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

下列範例說明如何將工作負載群組從其所屬集區移至預設集區。

ALTER WORKLOAD GROUP adHoc
USING [default];
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

另請參閱

SQL Server

SQL 受控執行個體

* Azure Synapse
Analytics *

Azure Synapse Analytics

變更現有的工作負載群組。

請參閱以下的 ALTER WORKLOAD GROUP 行為一節，進一步了解 ALTER WORKLOAD GROUP 如何在具有執行和佇列要求的系統上運作。

對 CREATE WORKLOAD GROUP 的限制也適用於 ALTER WORKLOAD GROUP。在修改參數之前，請查詢 sys.workload_management_workload_groups 以確保值位於可接受的範圍內。

語法

ALTER WORKLOAD GROUP group_name
WITH
([ MIN_PERCENTAGE_RESOURCE = value ]
  [ [ , ] CAP_PERCENTAGE_RESOURCE = value ]
  [ [ , ] REQUEST_MIN_RESOURCE_GRANT_PERCENT = value ]
  [ [ , ] REQUEST_MAX_RESOURCE_GRANT_PERCENT = value ]
  [ [ , ] IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH }]
  [ [ , ] QUERY_EXECUTION_TIMEOUT_SEC = value ] )
  [ ; ]

引數

group_name

是否已變更現有使用者定義的工作負載群組名稱。 group_name 不可變更。

MIN_PERCENTAGE_RESOURCE = value

value 為從 0 到 100 的整數範圍。變更 MIN_PERCENTAGE_RESOURCE 時，所有工作負載群組的 MIN_PERCENTAGE_RESOURCE 總和不可超過 100。如要變更 MIN_PERCENTAGE_RESOURCE，則必須先完成工作負載群組中正在執行的所有查詢，命令才會完成。如需詳細資訊，請參閱本文中的 ALTER WORKLOAD GROUP 行為一節。

CAP_PERCENTAGE_RESOURCE = value

value 是 1 到 100 的整數範圍。 CAP_PERCENTAGE_RESOURCE 的值必須大於 MIN_PERCENTAGE_RESOURCE。若要變更 CAP_PERCENTAGE_RESOURCE，則必須先完成工作負載群組中正在執行的所有查詢，命令才會完成。如需詳細資訊，請參閱本文中的 ALTER WORKLOAD GROUP 行為一節。

REQUEST_MIN_RESOURCE_GRANT_PERCENT = value

value 是介於 0.75 到 100.00 之間的十進位數。 REQUEST_MIN_RESOURCE_GRANT_PERCENT 的值必須是 MIN_PERCENTAGE_RESOURCE 的因數，且必須小於 CAP_PERCENTAGE_RESOURCE。

REQUEST_MAX_RESOURCE_GRANT_PERCENT = value

value 是十進位數，且必須大於 REQUEST_MIN_RESOURCE_GRANT_PERCENT。

IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH }

變更工作負載群組要求的預設重要性。

QUERY_EXECUTION_TIMEOUT_SEC = 值

變更查詢在取消之前可執行的時間上限 (以秒為單位)。值必須是 0 或正整數。值的預設設定為 0，這代表沒有限制。

權限

需要 CONTROL DATABASE 權限。

範例

下列範例會檢查工作負載群組 wgDataLoads 的目錄檢視中的值，並變更這些值。

SELECT *
FROM sys.workload_management_workload_groups
WHERE [name] = 'wgDataLoads'

ALTER WORKLOAD GROUP wgDataLoads WITH
( MIN_PERCENTAGE_RESOURCE            = 40
, CAP_PERCENTAGE_RESOURCE            = 80
, REQUEST_MIN_RESOURCE_GRANT_PERCENT = 10 )

ALTER WORKLOAD GROUP 行為

系統中無論何時都有三種類型的要求：

尚未分類的要求。
已分類且正在等待物件鎖定或系統資源的要求。
已分類且正在執行的要求。

設定的生效時間會隨著工作負載群組屬性變更而不同。

重要性或 query_execution_timeout

針對重要性和 query_execution_timeout 屬性，未分類的要求會挑選新的設定值。正在等待和執行的要求會按舊組態執行。無論工作負載群組中是否有正在執行的查詢，ALTER WORKLOAD GROUP 要求都會立即執行。

REQUEST_MIN_RESOURCE_GRANT_PERCENT 或 REQUEST_MAX_RESOURCE_GRANT_PERCENT

針對 REQUEST_MIN_RESOURCE_GRANT_PERCENT 和 REQUEST_MAX_RESOURCE_GRANT_PERCENT，執行中的要求會以舊設定執行。正在等待的要求和未分類要求會挑選新組態值。無論工作負載群組中是否有正在執行的查詢，ALTER WORKLOAD GROUP 要求都會立即執行。

MIN_PERCENTAGE_RESOURCE 或 CAP_PERCENTAGE_RESOURCE

針對 MIN_PERCENTAGE_RESOURCE 和 CAP_PERCENTAGE_RESOURCE，執行中的要求會以舊設定執行。正在等待的要求和未分類要求會挑選新組態值。

要變更 MIN_PERCENTAGE_RESOURCE 和 CAP_PERCENTAGE_RESOURCE，必須清空要變更的工作負載群組中正在執行的要求。減少 MIN_PERCENTAGE_RESOURCE 時，釋出的資源會傳回共用集區，讓其他工作負載群組中的要求能夠利用。相反地，增加 MIN_PERCENTAGE_RESOURCE 則會等候直到只使用共用集區所需資源的要求完成。 ALTER WORKLOAD GROUP 作業將具有共用資源的優先存取權，在共用集區中等待執行的其他要求則往後排。如果 MIN_PERCENTAGE_RESOURCE 的總和超過 100%，ALTER WORKLOAD GROUP 要求將立即失敗。

鎖定行為

要變更工作負載群組，必須對所有工作負載群組執行全域鎖定。變更工作負載群組的要求會排入已提交的 create 或 drop 工作負載群組要求的後面。如果同時提交了一批 alter 陳述式，則會依照其提交順序加以處理。

ALTER WORKLOAD GROUP (Transact-SQL)

選取產品

SQL Server 與 SQL 受控執行個體

語法

引數

group_name | "default"

IMPORTANCE = { LOW | MEDIUM | HIGH }

REQUEST_MAX_MEMORY_GRANT_PERCENT = value

REQUEST_MAX_CPU_TIME_SEC = value

REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value

MAX_DOP = value

GROUP_MAX_REQUESTS = value

USING { pool_name | "default" }

備註

在分割資料表上建立索引

權限

範例

另請參閱

SQL Server 與 SQL 受控執行個體

語法

引數

group_name | "default"

IMPORTANCE = { LOW | MEDIUM | HIGH }

REQUEST_MAX_MEMORY_GRANT_PERCENT = value

REQUEST_MAX_CPU_TIME_SEC = value

REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value

MAX_DOP = value

GROUP_MAX_REQUESTS = value

USING { pool_name | "default" }

備註

在分割資料表上建立索引

權限

範例

另請參閱

Azure Synapse Analytics

語法

引數

group_name

MIN_PERCENTAGE_RESOURCE = value

CAP_PERCENTAGE_RESOURCE = value

REQUEST_MIN_RESOURCE_GRANT_PERCENT = value

REQUEST_MAX_RESOURCE_GRANT_PERCENT = value

IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH }

QUERY_EXECUTION_TIMEOUT_SEC = 值

權限

範例

ALTER WORKLOAD GROUP 行為

重要性或 query_execution_timeout

REQUEST_MIN_RESOURCE_GRANT_PERCENT 或 REQUEST_MAX_RESOURCE_GRANT_PERCENT

MIN_PERCENTAGE_RESOURCE 或 CAP_PERCENTAGE_RESOURCE

鎖定行為

另請參閱

意見反應

意見反應

其他資源