ALTER WORKLOAD GROUP (Transact-SQL)ALTER WORKLOAD GROUP (Transact-SQL)

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

變更現有的資源管理員工作負載群組設定,並選擇性地將其指派給資源管理員資源集區。Changes an existing Resource Governor workload group configuration, and optionally assigns it to a Resource Governor resource pool.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions.

語法Syntax

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" } ]  
[ ; ]  

引數Arguments

group_name | "default"group_name | "default"
這是現有使用者定義之工作負載群組的名稱,或是資源管理員預設工作負載群組的名稱。Is the name of an existing user-defined workload group or the Resource Governor default workload group.

注意

當您安裝 SQL ServerSQL Server 時,資源管理員就會建立此 "default" 和內部群組。Resource Governor creates the "default" and internal groups when SQL ServerSQL Server is installed.

搭配 ALTER WORKLOAD GROUP 使用時,"default" 選項必須加上引號 ("") 或方括號 ([]) 才能避免與系統保留字 DEFAULT 產生衝突。The option "default" must be enclosed by quotation marks ("") or brackets ([]) when used with ALTER WORKLOAD GROUP to avoid conflict with DEFAULT, which is a system reserved word. 如需詳細資訊,請參閱< Database Identifiers>。For more information, see Database Identifiers.

注意

預先定義的工作負載群組和資源集區都會使用小寫名稱,例如 "default"。Predefined workload groups and resource pools all use lowercase names, such as "default". 如果是使用區分大小寫之定序的伺服器,則應該將此列入考量。This should be taken into account for servers that use case-sensitive collation. 具有不區分大小寫之定序 (如 SQL_Latin1_General_CP1_CI_AS) 的伺服器會將 "default" 和 "Default" 視為相同。Servers with case-insensitive collation, such as SQL_Latin1_General_CP1_CI_AS, will treat "default" and "Default" as the same.

IMPORTANCE = { LOW | MEDIUM | HIGH }IMPORTANCE = { LOW | MEDIUM | HIGH }
指定要求在工作負載群組中的相對重要性。Specifies the relative importance of a request in the workload group. 重要性為下列其中一項:Importance is one of the following:

  • LOWLOW
  • MEDIUM (預設值)MEDIUM (default)
  • HIGHHIGH

注意

每個重要性設定在內部都會儲存為計算所使用的數字。Internally each importance setting is stored as a number that is used for calculations.

IMPORTANCE 的資源集區範圍為本機;相同資源集區內部不同重要性的工作負載群組會彼此影響,但不會影響另一個資源集區中的工作負載群組。IMPORTANCE is local to the resource pool; workload groups of different importance inside the same resource pool affect each other, but do not affect workload groups in another resource pool.

REQUEST_MAX_MEMORY_GRANT_PERCENT = value REQUEST_MAX_MEMORY_GRANT_PERCENT = value
指定單一要求可由集區中獲取的記憶體最大數量。Specifies the maximum amount of memory that a single request can take from the pool. value 是相對於 MAX_MEMORY_PERCENT 所指定的資源集區大小的百分比。value is a percentage relative to the resource pool size specified by MAX_MEMORY_PERCENT.

value 是最高到 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 的整數、從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始的浮點數。value is an integer up to SQL Server 2017 (14.x)SQL Server 2017 (14.x) and a float starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x). 預設值為 25。Default value is 25. 允許的 value 範圍為 1 至 100。The allowed range for value is from 1 through 100.

注意

指定的數量僅參考查詢執行授與記憶體。The amount specified only refers to query execution grant memory.

重要

value 設定為 0 會避免在使用者定義的工作負載群組中,執行具有 SORT 和 HASH JOIN 作業的查詢。Setting value to 0 prevents queries with SORT and HASH JOIN operations in user-defined workload groups from running.

不建議您將 value 設定為大於 70,因為如果其他並行查詢正在執行,伺服器可能無法將足夠的記憶體擱置在一旁。It is not recommended to set value greater than 70 because the server may be unable to set aside enough free memory if other concurrent queries are running. 最後,這可能會導致查詢逾時錯誤 8645。This may eventually lead to query time-out error 8645.

注意

如果查詢記憶體需求超過這個參數所指定的限制,伺服器會執行以下作業:If the query memory requirements exceed the limit that is specified by this parameter, the server does the following:

  • 如果是使用者定義的工作負載群組,伺服器會嘗試減少查詢的平行處理原則程度,直到記憶體需求低於此限制,或是直到平行處理原則程度等於 1 為止。For user-defined workload groups, the server tries to reduce the query degree of parallelism until the memory requirement falls under the limit, or until the degree of parallelism equals 1. 如果查詢記憶體需求仍然大於此限制,將會發生錯誤 8657。If the query memory requirement is still greater than the limit, error 8657 occurs.

  • 如果是內部和預設的工作負載群組,伺服器會允許查詢取得所需的記憶體。For internal and default workload groups, the server permits the query to obtain the required memory.

請注意,如果伺服器沒有足夠的實體記憶體,這兩種情況都會受限於逾時錯誤 8645。Be aware that both cases are subject to time-out error 8645 if the server has insufficient physical memory.

REQUEST_MAX_CPU_TIME_SEC = value REQUEST_MAX_CPU_TIME_SEC = value
指定要求可以使用的最大 CPU 時間量 (以秒為單位)。Specifies the maximum amount of CPU time, in seconds, that a request can use. value 必須是 0 或正整數。value must be 0 or a positive integer. value 的預設設定為 0,這代表沒有限制。The default setting for value is 0, which means unlimited.

注意

根據預設,Resource Governor 不會在超過最大時間時,阻止要求繼續執行。By default, Resource Governor will not prevent a request from continuing if the maximum time is exceeded. 不過,系統將會產生某個事件。However, an event will be generated. 如需詳細資訊,請參閱超過 CPU 閾值事件類別For more information, see CPU Threshold Exceeded Event Class.

重要

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始,並使用追蹤旗標 2422,Resource Governor 將在超過時間上限時中止要求。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3, and using trace flag 2422, Resource Governor will abort a request when the maximum time is exceeded.

REQUEST_MEMORY_GRANT_TIMEOUT_SEC =valueREQUEST_MEMORY_GRANT_TIMEOUT_SEC =value
指定查詢能夠等待記憶體授權 (工作緩衝區記憶體) 變成可用的最大時間 (以秒為單位)。Specifies the maximum time, in seconds, that a query can wait for memory grant (work buffer memory) to become available.

注意

到達記憶體授權的逾時值時,查詢不一定會失敗。A query does not always fail when memory grant time-out is reached. 只有當有太多並行的查詢正在執行時,查詢才會失敗。A query will only fail if there are too many concurrent queries running. 否則,查詢可能只會得到最小的記憶體授權,導致查詢效能降低。Otherwise, the query may only get the minimum memory grant, resulting in reduced query performance.

value 必須為正整數。value must be a positive integer. value 的預設設定 0 會根據查詢成本使用內部計算來判斷最大時間。The default setting for value, 0, uses an internal calculation based on query cost to determine the maximum time.

MAX_DOP =value MAX_DOP =value
為平行要求指定平行處理原則的最大程度 (DOP)。Specifies the maximum degree of parallelism (DOP) for parallel requests. value 必須是 0 或正整數 (1 到 255)。value must be 0 or a positive integer, 1 though 255. value 為 0 時,伺服器會選擇平行處理原則的最大程度。When value is 0, the server chooses the max degree of parallelism. 這是預設值且為建議的設定。This is the default and recommended setting.

注意

Database EngineDatabase Engine 針對 MAX_DOP 所設定的實際值可能會小於指定的值。The actual value that the Database EngineDatabase Engine sets for MAX_DOP by might be less than the specified value. 最終的值是由公式 min(255, CPU 的數目) 所決定。The final value is determined by the formula min(255, number of CPUs).

警告

變更 MAX_DOP 可能會對伺服器的效能造成不良影響。Changing MAX_DOP can adversely affect a server's performance. 如果您必須變更 MAX_DOP,我們建議您將它設定為小於或等於存在單一 NUMA 節點中之最大硬體排程器數目的值。If you must change MAX_DOP, we recommend that it be set to a value that is less than or equal to the maximum number of hardware schedulers that are present in a single NUMA node. 我們建議您不要將 MAX_DOP 設定為大於 8 的值。We recommend that you do not set MAX_DOP to a value greater than 8.

MAX_DOP 會以下列方式處理:MAX_DOP is handled as follows:

  • 只要 MAX_DOP 沒有超過工作負載群組 MAX_DOP,就會接受當做查詢提示的 MAX_DOP。MAX_DOP as a query hint is honored as long as it does not exceed workload group MAX_DOP.

  • 當做查詢提示的 MAX_DOP 永遠會覆寫 sp_configure 的「平行處理原則的最大程度」。MAX_DOP as a query hint always overrides sp_configure 'max degree of parallelism'.

  • 工作負載群組 MAX_DOP 會覆寫 sp_configure 的「平行處理原則的最大程度」。Workload group MAX_DOP overrides sp_configure 'max degree of parallelism'.

  • 如果查詢在編譯時間被標示為序列 (MAX_DOP = 1),則不管工作負載群組或 sp_configure 設定為何,都無法在執行階段將該查詢變更回平行。If the query is marked as serial (MAX_DOP = 1) at compile time, it cannot be changed back to parallel at run time regardless of the workload group or sp_configure setting.

DOP 經過設定後,在授與記憶體不足的壓力下,僅能將其降低。After DOP is configured, it can only be lowered on grant memory pressure. 在授與記憶體佇列中等候時,看不到工作負載群組的重新組態。Workload group reconfiguration is not visible while waiting in the grant memory queue.

GROUP_MAX_REQUESTS = value GROUP_MAX_REQUESTS = value
指定在工作負載群組中可允許執行的最大同時要求數。Specifies the maximum number of simultaneous requests that are allowed to execute in the workload group. value 必須是 0 或正整數。value must be 0 or a positive integer. value 的預設設定 0 允許無限制的要求。The default setting for value, 0, allows unlimited requests. 達到最大並行要求時,該群組中的使用者可以登入,但是會處於等候狀態,直到並行要求低於指定的值為止。When the maximum concurrent requests are reached, a user in that group can log in, but is placed in a wait state until concurrent requests are dropped below the value specified.

USING { pool_name | "default" }USING { pool_name | "default" }
將工作負載群組與 pool_name 所識別的使用者定義資源集區產生關聯,實際上會將工作負載群組放入資源集區中。Associates the workload group with the user-defined resource pool identified by pool_name, which in effect puts the workload group in the resource pool. 如果未提供 pool_name 或未使用 USING 引數,工作負載群組會放入預先定義的資源管理員預設集區。If pool_name is not provided or if the USING argument is not used, the workload group is put in the predefined Resource Governor default pool.

搭配 ALTER WORKLOAD GROUP 使用時,"default" 選項必須加上引號 ("") 或方括號 ([]) 才能避免與系統保留字 DEFAULT 產生衝突。The option "default" must be enclosed by quotation marks ("") or brackets ([]) when used with ALTER WORKLOAD GROUP to avoid conflict with DEFAULT, which is a system reserved word. 如需詳細資訊,請參閱< Database Identifiers>。For more information, see Database Identifiers.

注意

"default" 選項會區分大小寫。The option "default" is case-sensitive.

RemarksRemarks

在預設群組中允許使用 ALTER WORKLOAD GROUP。ALTER WORKLOAD GROUP is allowed on the default group.

工作負載群組組態的變更在執行 ALTER RESOURCE GOVERNOR RECONFIGURE 前不會生效。Changes to the workload group configuration do not take effect until after ALTER RESOURCE GOVERNOR RECONFIGURE is executed. 當變更計劃影響設定時,只有在執行 DBCC FREEPROCCACHE (pool_name) 之後新的設定才會在先前已快取的計劃中生效,其中 pool_name 是工作負載群組相關聯之 Resource Governor 資源集區的名稱。When changing a plan affecting setting, the new setting will only take effect in previously cached plans after executing DBCC FREEPROCCACHE (pool_name), where pool_name is the name of a Resource Governor resource pool on which the workload group is associated with.

  • 如果將 MAX_DOP 變更為 1,則不需要執行 DBCC FREEPROCCACHE 因為平行計畫可以在序列模式中執行。If changing MAX_DOP to 1, executing DBCC FREEPROCCACHE is not required because parallel plans can run in serial mode. 不過,它可能不會和編譯為序列計劃的計劃一樣有效率。However, it may not be as efficient as a plan compiled as a serial plan.

  • 如果將 MAX_DOP 從 1 變更為 0 或大於 1 的值,則不需要執行 DBCC FREEPROCCACHE。If changing MAX_DOP from 1 to 0 or a value greater than 1, executing DBCC FREEPROCCACHE is not required. 不過,序列計畫無法以平行方式執行,因此清除個別的快取將會允許新計劃有可能使用平行處理原則進行編譯。However, serial plans cannot run in parallel, so clearing the respective cache will allow new plans to potentially be compiled using parallelism.

警告

從與超過一個工作負載群組相關聯的資源集區清除已快取的計劃,會影響包含以 pool_name 所識別使用者定義資源集區的所有工作負載群組。Clearing cached plans from a resource pool that is associated with more than one workload group will affect all workload groups with the user-defined resource pool identified by pool_name.

要執行 DDL 陳述式時,建議您先熟悉資源管理員的狀態。When executing DDL statements, we recommend that you be familiar with Resource Governor states. 如需詳細資訊,請參閱 Resource GovernorFor more information, see Resource Governor.

REQUEST_MEMORY_GRANT_PERCENT:在 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 中,允許建立索引即可使用比一開始授與的記憶體更多工作區記憶體來改善效能。REQUEST_MEMORY_GRANT_PERCENT: In SQL Server 2005 (9.x)SQL Server 2005 (9.x), index creation is allowed to use more workspace memory than initially granted for improved performance. 資源管理員的更新版本中支援此特殊處理,不過,初始授與和任何額外的記憶體授與都會受到資源集區和工作負載群組設定的限制。This special handling is supported by Resource Governor in later versions, however, the initial grant and any additional memory grant are limited by resource pool and workload group settings.

在資料分割資料表上建立索引Index Creation on a Partitioned Table

非對齊式分割區資料表上之索引建立所耗用的記憶體,與相關的分割區數目成正比。The memory consumed by index creation on non-aligned partitioned table is proportional to the number of partitions involved. 如果所需的總記憶體超出資源管理員工作負載群組設定所設的每個查詢限制 (REQUEST_MAX_MEMORY_GRANT_PERCENT),這個索引建立動作就可能無法執行。If the total required memory exceeds the per-query limit (REQUEST_MAX_MEMORY_GRANT_PERCENT) imposed by the Resource Governor workload group setting, this index creation may fail to execute. 由於 "default" 工作負載群組允許查詢超過每個查詢限制,而且具有 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 相容性啟動所需的記憶體下限,因此使用者或許能夠在 "default" 工作負載群組中執行相同的索引建立動作,但前提是 "default" 資源集區有設定足夠的總記憶體來執行這類查詢。Because the "default" workload group allows a query to exceed the per-query limit with the minimum required memory to start for SQL Server 2005 (9.x)SQL Server 2005 (9.x) compatibility, the user may be able to run the same index creation in "default" workload group, if the "default" resource pool has enough total memory configured to run such query.

權限Permissions

需要 CONTROL SERVER 權限。Requires CONTROL SERVER permission.

範例Examples

下列範例顯示如何將預設群組中的要求重要性從 MEDIUM 變更為 LOWThe following example shows how to change the importance of requests in the default group from MEDIUM to LOW.

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

下列範例顯示如何將工作負載群組從所屬集區移到預設集區。The following example shows how to move a workload group from the pool that it's in to the default pool.

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

另請參閱See Also

資源管理員 Resource Governor
CREATE WORKLOAD GROUP (Transact-SQL) CREATE WORKLOAD GROUP (Transact-SQL)
DROP WORKLOAD GROUP (Transact-SQL) DROP WORKLOAD GROUP (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL) CREATE RESOURCE POOL (Transact-SQL)
ALTER RESOURCE POOL (Transact-SQL) ALTER RESOURCE POOL (Transact-SQL)
DROP RESOURCE POOL (Transact-SQL) DROP RESOURCE POOL (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)ALTER RESOURCE GOVERNOR (Transact-SQL)