CREATE WORKLOAD GROUP (Transact-SQL)CREATE WORKLOAD GROUP (Transact-SQL)

按一下產品!Click a product!

在下列資料列中,按一下您感興趣的產品名稱。In the following row, click whichever product name you're interested in. 視您所按下的產品而定,此點選會在本網頁的這裡顯示不同的內容。The click displays different content here on this webpage, appropriate for whichever product you click.

* SQL Server *  * SQL Server *   SQL Database
受控執行個體
SQL Database
managed instance
Azure Synapse
Analytics
Azure Synapse
Analytics

 

SQL Server 和 SQL Database 受控執行個體SQL Server and SQL Database managed instance

建立資源管理員工作負載群組,並將工作負載群組與資源管理員資源集區產生關聯。Creates a Resource Governor workload group and associates the workload group with a Resource Governor resource pool. 並非每個 MicrosoftMicrosoftSQL ServerSQL Server 版本中都可使用 Resource Governor。Resource Governor is not available in every edition of MicrosoftMicrosoftSQL ServerSQL Server. 如需 SQL ServerSQL Server版本支援的功能清單,請參閱 SQL Server 2016 版本支援的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016.

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

語法Syntax

CREATE WORKLOAD GROUP group_name
[ 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" ]
    [ [ , ] EXTERNAL external_pool_name | "default" ] ]
    } ]
[ ; ]

引數Arguments

group_name 是工作負載群組的使用者定義名稱。group_name Is the user-defined name for the workload group. group_name 是英數字元,最多可有 128 個字元,而且在 SQL ServerSQL Server 的執行個體內必須是唯一的,並須符合識別碼的規則。group_name is alphanumeric, can be up to 128 characters, must be unique within an instance of SQL ServerSQL Server, and must comply with the rules for identifiers.

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

  • 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 指定要求可以使用的最大 CPU 時間量 (以秒為單位)。REQUEST_MAX_CPU_TIME_SEC = value 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 = value 指定查詢能夠等待記憶體授權 (工作緩衝區記憶體) 變成可用的最大時間 (以秒為單位)。REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value Specifies the maximum time, in seconds, that a query can wait for a memory grant (work buffer memory) to become available. value 必須是 0 或正整數。value must be 0 or a positive integer. value 的預設設定 0 會根據查詢成本使用內部計算來判斷最大時間。The default setting for value, 0, uses an internal calculation based on query cost to determine the maximum time.

注意

到達記憶體授權的逾時值時,查詢不一定會失敗。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.

MAX_DOP = value,為平行查詢執行指定平行處理原則的最大程度 (MAXDOP)MAX_DOP = value Specifies the maximum degree of parallelism (MAXDOP) for parallel query execution. value 必須是 0 或正整數。value must be 0 or a positive integer. value 允許範圍是從 0 至 64。The allowed range for value is from 0 through 64. value 的預設設定 0 會使用全域設定。The default setting for value, 0, uses the global setting. MAX_DOP 會以下列方式處理:MAX_DOP is handled as follows:

注意

工作負載群組 MAX_DOP 會覆寫平行處理原則的最大程度伺服器組態MAXDOP 資料庫範圍組態Workload group MAX_DOP overrides the server configuration for max degree of parallelism and the MAXDOP database scoped configuration.

提示

若要在查詢層級完成此操作,請使用 MAXDOP 查詢提示To accomplish this at the query level, use the MAXDOP query hint. 將平行處理原則的最大程度設定為查詢提示非常有效,只要它不預期工作負載群組 MAX_DOP。Setting the maximum degree of parallelism as a query hint is effective as long as it does not exceed the workload group MAX_DOP. 如果 MAXDOP 查詢提示值超過使用 Resource Governor 所設定的值,SQL Server Database EngineSQL Server Database Engine 就會使用 Resource Governor MAX_DOP 值。If the MAXDOP query hint value exceeds the value that is configured by using the Resource Governor, the SQL Server Database EngineSQL Server Database Engine uses the Resource Governor MAX_DOP value. MAXDOP 查詢提示一律會覆寫平行處理原則的最大程度伺服器組態The MAXDOP query hint always overrides the server configuration for max degree of parallelism.

若要在資料庫層級完成此操作,請使用 MAXDOP 資料庫範圍設定To accomplish this at the database level, use the MAXDOP database scoped configuration.

若要在伺服器層級完成此操作,請使用平行處理原則的最大程度 (MAXDOP) 伺服器組態選項To accomplish this at the server level, use the max degree of parallelism (MAXDOP) server configuration option.

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 a 0 or a positive integer. value 的預設值為 0,會允許無限制的要求。The default setting for value is 0, and 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" } 建立工作負載群組與 pool_name 識別之使用者定義資源集區的關聯。USING { pool_name | "default" } Associates the workload group with the user-defined resource pool identified by pool_name. 這樣會將工作負載群組實際放到資源集區中。This in effect puts the workload group in the resource pool. 如果未提供 pool_name 或未使用 USING 引數,工作負載群組會放入預先定義的 Resource Governor 預設集區。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.

"default" 是保留字,而且當搭配 USING 使用時,必須加上引號 ("") 或方括號 ([])。"default" is a reserved word and when used with USING, must be enclosed by quotation marks ("") or brackets ([]).

注意

預先定義的工作負載群組和資源集區都會使用小寫名稱,例如 "default"。Predefined workload groups and resource pools all use lower case 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.

EXTERNAL external_pool_name | "default" 適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) 和更新版本)。EXTERNAL external_pool_name | "default" Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later).

工作負載群組可指定外部資源集區。Workload group can specify an external resource pool. 您可以定義工作負載群組,並與兩個集區產生關聯:You can define a workload group and associate with two pools:

備註Remarks

使用 REQUEST_MEMORY_GRANT_PERCENT 時,在建立索引時將可使用比一開始授與的記憶體更多的工作空間記憶體來改善效能。When REQUEST_MEMORY_GRANT_PERCENT is used, index creation is allowed to use more workspace memory than what is initially granted for improved performance. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中,資源管理員支援這種特殊的處理。This special handling is supported by Resource Governor in SQL Server 2019 (15.x)SQL Server 2019 (15.x). 不過,初始授與和任何額外的記憶體授與都受到資源集區和工作負載群組設定的限制。However, the initial grant and any additional memory grant are limited by resource pool and workload group settings.

MAX_DOP 限制是以工作為基礎。The MAX_DOP limit is set per task. 它不是根據要求或查詢限制。It is not a per request or per query limit. 這表示在平行查詢執行期間,單一要求可能會繁衍指派至排程器的多個工作。This means that during a parallel query execution, a single request can spawn multiple tasks which are assigned to a scheduler. 如需詳細資訊,請參閱執行緒與工作架構指南For more information, see the Thread and Task Architecture Guide.

當使用 MAX_DOP 且查詢在編譯時間被標示為序列,不管工作負載群組或伺服器組態設定為何,都無法在執行階段將該查詢變更回平行。When MAX_DOP is used and a query is marked as serial at compile time, it cannot be changed back to parallel at run time regardless of the workload group or server configuration setting. 在設定 MAX_DOP 之後,只有在由於記憶體壓力的情況下才能將它降低。After MAX_DOP is configured, it can only be lowered due to memory pressure. 在授與記憶體佇列中等候時,看不到工作負載群組的重新組態。Workload group reconfiguration is not visible while waiting in the grant memory queue.

分割區資料表上的索引建立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" 工作負載群組允許查詢超過每個查詢限制,而且具有所需的記憶體下限,因此使用者或許能夠在 "default" 工作負載群組中執行相同的索引建立動作,但前提是 "default" 資源集區有設定足夠的總記憶體來執行這類查詢。Because the "default" workload group allows a query to exceed the per-query limit with the minimum required memory, 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.

範例Example

建立名為 newReports 的工作負載群組,它會使用 Resource Governor 預設設定,而且位於 Resource Governor 預設集區中。Create a workload group named newReports which uses the Resource Governor default settings, and is in the Resource Governor default pool. 此範例會指定 default 集區,但這不是必要的。The example specifies the default pool, but this is not required.

CREATE WORKLOAD GROUP newReports
    USING "default" ;
GO

另請參閱See Also

SQL ServerSQL Server SQL Database
受控執行個體
SQL Database
managed instance
* Azure Synapse
Analytics *
 
* Azure Synapse
Analytics *
 

 

Azure Synapse Analytics (預覽)Azure Synapse Analytics (preview)

建立工作負載群組。Creates a workload group. 工作負載群組是一組要求的容器,而且是在系統上設定工作負載管理的基礎。Workload groups are containers for a set of requests and are the basis for how workload management is configured on a system. 工作負載群組提供保留資源以進行工作負載隔離、包含資源、定義每個要求的資源,以及遵守執行規則的能力。Workload groups provide the ability to reserve resources for workload isolation, contain resources, define resources per request, and adhere to execution rules. 一旦陳述式完成,設定就會生效。Once the statement completes, the settings are in effect.

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

CREATE 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_namegroup_name
指定用來識別工作負載群組的名稱。Specifies the name by which the workload group is identified. group_name 是一種 sysname。group_name is a sysname. 它的長度最多可以是 128 個字元,且在執行個體內必須是唯一的。It can be up to 128 characters long and must be unique within the instance.

MIN_PERCENTAGE_RESOURCE = 值MIN_PERCENTAGE_RESOURCE = value
針對該工作負載群組指定一個保證的最小資源配置,該資源不會與其他工作負載群組共用。Specifies a guaranteed minimum resource allocation for this workload group that is not shared with other workload groups. value 為從 0 到 100 的整數範圍。value is an integer range from 0 to 100. 所有工作負載群組之間的 min_percentage_resource 總和不能超過 100。The sum of min_percentage_resource across all workload groups cannot exceed 100. min_percentage_resource 的值不能大於 cap_percentage_resource。The value for min_percentage_resource cannot be greater than cap_percentage_resource. 每個服務等級允許最小的有效值。There are minimum effective values allowed per service level. 如需詳細資訊,請參閱有效值See Effective Values for more details.

CAP_PERCENTAGE_RESOURCE = 值CAP_PERCENTAGE_RESOURCE = value
指定工作負載群組中所有要求的資源使用率上限。Specifies the maximum resource utilization for all requests in a workload group. 允許的整數值範圍為 1 到 100。The allowed integer range for value is 1 through 100. cap_percentage_resource 的值必須大於 min_percentage_resource。The value for cap_percentage_resource must be greater than min_percentage_resource. 如果 min_percentage_resource 在其他工作負載群組中設定為大於零,則可以減小 cap_percentage_resource 的有效值。The effective value for cap_percentage_resource can be reduced if min_percentage_resource is configured greater than zero in other workload groups.

REQUEST_MIN_RESOURCE_GRANT_PERCENT = 值REQUEST_MIN_RESOURCE_GRANT_PERCENT = value
設定每個要求配置的最小資源量。Sets the minimum amount of resources allocated per request. value 為必要的參數,其十進位範圍介於 0.75 到 100.00 之間。value is a required parameter with a decimal range between 0.75 to 100.00. request_min_resource_grant_percent 的值必須是 0.25 的倍數,必須是 min_percentage_resource 的因數,而且小於 cap_percentage_resource。The value for request_min_resource_grant_percent must be a multiple of 0.25, must be a factor of min_percentage_resource, and be less than cap_percentage_resource. 每個服務等級允許最小的有效值。There are minimum effective values allowed per service level. 如需詳細資訊,請參閱有效值See Effective Values for more details.

例如:For example:

CREATE WORKLOAD GROUP wgSample WITH
( MIN_PERCENTAGE_RESOURCE = 26              -- integer value
 ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 3.25 -- factor of 26 (guaranteed a minimum of 8 concurrency)
 ,CAP_PERCENTAGE_RESOURCE = 100 )

請考慮用於資源類別的值,作為 request_min_resource_grant_percent 的指導方針。Consider the values that are used for resource classes as a guideline for request_min_resource_grant_percent. 下表包含 Gen2 的資源配置。The table below contains resource allocations for Gen2.

資源類別Resource Class 資源百分比Percent of Resources
SmallrcSmallrc 3%3%
MediumrcMediumrc 10%10%
LargercLargerc 22%22%
XlargercXlargerc 70%70%

REQUEST_MAX_RESOURCE_GRANT_PERCENT = 值REQUEST_MAX_RESOURCE_GRANT_PERCENT = value
設定每個要求配置的資源數量上限。Sets the maximum amount of resources allocated per request. value 為選擇性參數,預設值等於 request_min_resource_grant_percent。value is an optional decimal parameter with a default value equal to the request_min_resource_grant_percent. value 必須大於或等於 request_min_resource_grant_percent。value must be greater than or equal to request_min_resource_grant_percent. 當 equest_max_resource_grant_percent 的值大於 request_min_resource_grant_percent 而且有可用的系統資源時,會將其他資源配置給要求。When the value of request_max_resource_grant_percent is greater than request_min_resource_grant_percent and system resources are available, additional resources are allocated to a request.

IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH }IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH }
指定要求在工作負載群組中的相對重要性。Specifies the default importance of a request for the workload group. 下列任一個為其重要性,其中 NORMAL 為預設值:Importance is one of the following, with NORMAL being the default:

  • LOWLOW
  • BELOW_NORMALBELOW_NORMAL
  • NORMAL (預設)NORMAL (default)
  • ABOVE_NORMALABOVE_NORMAL
  • HIGHHIGH

在工作負載群組中設定的重要性,是工作負載群組中所有要求的預設重要性。Importance set at the workload group is a default importance for all requests in the workload group. 使用者也可以在分類器層級設定重要性,該重要性可以覆寫工作負載群組的重要性設定。A user can also set importance at the classifier level, which can override the workload group importance setting. 這樣可以區分工作負載群組內要求的重要性,以便更快速地存取非保留的資源。This allows for differentiation of importance for requests within a workload group to get access to non-reserved resources quicker. 當跨工作負載群組的 min_percentage_resource 總和小於 100 時,會根據重要性來指派非保留的資源。When the sum of min_percentage_resource across workload groups is less than 100, there are non-reserved resources that are assigned on a basis of importance.

QUERY_EXECUTION_TIMEOUT_SEC = 值QUERY_EXECUTION_TIMEOUT_SEC = value
指定查詢在取消之前可以執行的最長時間 (以秒為單位)。Specifies the maximum time, in seconds, that a query can execute before it is canceled. value 必須是 0 或正整數。value must be 0 or a positive integer. 值的預設設定為 0,這代表查詢永不逾時。QUERY_EXECUTION_TIMEOUT_SEC 會在查詢處於執行中狀態時計算,而非在查詢排入佇列時。The default setting for value is 0, which the query never times out. QUERY_EXECUTION_TIMEOUT_SEC counts once the query is in running state, not when the query is queued.

備註Remarks

系統會針對回溯相容性自動建立對應至資源類別的工作負載群組。Workload groups corresponding to resource classes are created automatically for backward compatibility. 這些系統定義的工作負載群組無法卸除。These system defined workload groups cannot be dropped. 可以建立額外 8 位使用者定義的工作負載群組。An additional 8 user defined workload groups can be created.

如果工作負載群組是在 min_percentage_resource 大於零的情況下建立,CREATE WORKLOAD GROUP 陳述式將會排入佇列,直到有足夠的資源可建立工作負載群組為止。If a workload group is created with min_percentage_resource greater than zero, the CREATE WORKLOAD GROUP statement will queue until there are enough resources to create the workload group.

有效值Effective Values

min_percentage_resource、cap_percentage_resource、request_min_resource_grant_percent 和 request_max_resource_grant_percent 參數具有在目前服務層級和其他工作負載群組的內容中調整的有效值。The parameters min_percentage_resource, cap_percentage_resource, request_min_resource_grant_percent and request_max_resource_grant_percent have effective values that are adjusted in the context of the current service level and the configuration of other workload groups.

每個服務層級支援的並行與使用資源類定義每個查詢的資源授與時相同,因此,request_min_resource_grant_percent 的支援值取決於執行個體所設定的服務層級。The supported concurrency per service level remains the same as when resource classes were used to define resource grants per query, hence, the supported values for request_min_resource_grant_percent is dependent on the service level the instance is set to. 在最低服務層級 DW100c,每個要求需要最少 25% 的資源。At the lowest service level, DW100c, a minimum 25% resources per request is needed. 在 DW100c,已設定之工作負載群組的有效 request_min_resource_grant_percent 可以是 25% 以上。At DW100c, the effective request_min_resource_grant_percent for a configured workload group can be 25% or higher. 請參閱下表,以取得如何衍生有效值的進一步詳細資料。See the below table for further details on how effective values are derived.

服務等級Service Level REQUEST_MIN_RESOURCE_GRANT_PERCENT 的最低有效值Lowest effective value for REQUEST_MIN_RESOURCE_GRANT_PERCENT 並行查詢數目上限Maximum concurrent queries
DW100cDW100c 25%25% 44
DW200cDW200c 12.5%12.5% 88
DW300cDW300c 8%8% 1212
DW400cDW400c 6.25%6.25% 1616
DW500cDW500c 5%5% 2020
DW1000cDW1000c 3%3% 3232
DW1500cDW1500c 3%3% 3232
DW2000cDW2000c 2%2% 4848
DW2500cDW2500c 2%2% 4848
DW3000cDW3000c 1.5%1.5% 6464
DW5000cDW5000c 1.5%1.5% 6464
DW6000cDW6000c 0.75%0.75% 128128
DW7500cDW7500c 0.75%0.75% 128128
DW10000cDW10000c 0.75%0.75% 128128
DW15000cDW15000c 0.75%0.75% 128128
DW30000cDW30000c 0.75%0.75% 128128

同樣地,request_min_resource_grant_percent、min_percentage_resource 必須大於或等於有效的 request_min_resource_grant_percent。Similarly, request_min_resource_grant_percent, min_percentage_resource must be greater than or equal to the effective request_min_resource_grant_percent. min_percentage_resource 設定為小於有效 min_percentage_resource 的工作負載群組,其值在執行階段會調整為零。A workload group with min_percentage_resource configured that is less than effective min_percentage_resource has the value adjusted to zero at run time. 發生這種情況時,針對 min_percentage_resource 設定的資源可在所有工作負載群組間共用。When this happens, the resources configured for min_percentage_resource are sharable across all workload groups. 例如,min_percentage_resource 為 10% 且在 DW1000c 上執行的工作負載群組 wgAdHoc,會有 10% 的有效 min_percentage_resource (3.25% 是 DW1000c 所支援的最小值)。For example, the workload group wgAdHoc with a min_percentage_resource of 10% running at DW1000c would have an effective min_percentage_resource of 10% (3.25% is the minimum supported value at DW1000c). DW100c 的 wgAdhoc 的有效 min_percentage_resource 為 0%。wgAdhoc at DW100c would have an effective min_percentage_resource of 0%. 針對 wgAdhoc 設定的 10% 會在所有工作負載群組之間共用。The 10% configured for wgAdhoc would be shared across all workload groups.

cap_percentage_resource也具有有效值。Cap_percentage_resource also has an effective value. 如果工作負載群組 wgAdhoc 是以 100% cap_percentage_resource 設定,而另一個工作負載群組 wgDashboards 是以 25% min_percentage_resource 建立,則 wgAdhoc 的有效 cap_percentage_resource 會變成 75%。If a workload group wgAdhoc is configured with a cap_percentage_resource of 100% and another workload group wgDashboards is created with 25% min_percentage_resource, the effective cap_percentage_resource for wgAdhoc becomes 75%.

若要了解工作負載群組的執行階段值,最簡單的方式就是查詢系統檢視 sys.databases dm_workload_management_workload_groups_statsThe easiest way to understand the run-time values for your workload groups is to query the system view sys.dm_workload_management_workload_groups_stats.

權限Permissions

需要 CONTROL DATABASE 權限Requires CONTROL DATABASE permission

另請參閱See also