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 数据库
托管实例

SQL Databasemanaged instance |SQL 数据
数据仓库
SQL DataWarehouse|

 

SQL Server 和 SQL 数据库托管实例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 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 GroupName
是工作负荷组的用户定义名称。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 , 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 and a float starting with . 默认值为 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 Threshold Exceeded 事件类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 MAX_DOP = value
指定并行查询执行的最大并行度 (MAXDOP)。 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 查询提示值超出使用资源调控器配置的值,则 SQL Server 数据库引擎SQL Server Database Engine 使用资源调控器 MAX_DOP 值。If the MAXDOP query hint value exceeds the value that is configured by using the Resource Governor, the SQL Server 数据库引擎SQL 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" } USING { pool_name "default" }
将工作负荷组与由 pool_name 标识的用户定义的资源池关联起来 。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 参数,将工作负荷组放入预定义的资源调控器默认池 。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“EXTERNAL external_pool_name | "default"
适用于SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本)。 Applies to: ( 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. 如果所需的内存总量超过 Resource Governor 工作负荷组设置规定的每个查询的限制 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 的工作负荷组,它使用资源调控器默认设置并位于资源调控器默认池中。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 Server
SQL server SQL 数据库
托管实例

SQL Databasemanaged instance ||* SQL 数据
仓库*
 
SQL DataWarehouse||||

 

SQL 数据仓库(预览)SQL Data Warehouse (Preview)

创建工作负荷组。Initializes 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_name GroupName
指定用于标识工作负荷组的名称。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 = value MIN_PERCENTAGE_RESOURCE = value
指定为此工作负荷组保证的最小资源分配,这些资源不与其他工作负荷组共享。Specifies a guaranteed minimum resource allocation for this workload group that is not shared with other workload groups. 取值为 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 = value 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 = value 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 = value 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. 当 request_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 = value 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. value 的默认设置为 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. 例如,工作负荷组 wgAdHoc 的 min_percentage_resource 为 10%,在 DW1000c 服务级别运行,其有效 min_percentage_resource 则为 10%(DW1000c 支持的最低值为 3.25%)。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,并且创建另一个 min_percentage_resource 为 25% 的工作负荷组 wgDashboards,则 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.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

DROP WORKLOAD GROUP (Transact-SQL)DROP WORKLOAD GROUP (Transact-SQL)
sys.workload_management_workload_groupssys.workload_management_workload_groups
sys.dm_workload_management_workload_groups_statssys.dm_workload_management_workload_groups_stats
关于如何创建和使用工作负荷组的快速入门Quickstart on how to create and use a workload group