将资源类转换为工作负荷组Convert Resource Classes to Workload Groups

05/11/2020

本文内容

工作负荷组提供用于隔离和遏制系统资源的机制。Workload groups provide a mechanism to isolate and contain system resources.此外，工作负荷组允许针对其中运行的请求设置执行规则。Additionally, workload groups allow you to set execution rules for the requests running in them.使用查询超时执行规则可以取消失控的查询，而无需用户的干预。A query timeout execution rule allows runaway queries to be canceled without user intervention.本文介绍如何根据现有的资源类创建一个采用类似配置的工作负荷组。This article explains how to take an existing resource class and create a workload group with a similar configuration.此外，将添加一个可选的查询超时规则。In addition, an optional query timeout rule is added.

了解现有的资源类配置Understanding the existing resource class configuration

工作负荷组需要名为 REQUEST_MIN_RESOURCE_GRANT_PERCENT 的参数，该参数指定分配给每个请求的总体系统资源百分比。Workload groups require a parameter called REQUEST_MIN_RESOURCE_GRANT_PERCENT that specifies the percentage of overall system resources allocated per request.资源类的资源分配是通过分配并发槽实现的。Resource allocation is done for resource classes by allocating concurrency slots.若要确定 REQUEST_MIN_RESOURCE_GRANT_PERCENT 的值，请使用 sys.dm_workload_management_workload_groups_stats DMV。To determine the value to specify for REQUEST_MIN_RESOURCE_GRANT_PERCENT, use the sys.dm_workload_management_workload_groups_stats DMV.例如，以下查询将返回一个可用于 REQUEST_MIN_RESOURCE_GRANT_PERCENT 参数，以创建类似于 staticrc40 的工作负荷组的值。For example, the below query query returns a value that can be used for the REQUEST_MIN_RESOURCE_GRANT_PERCENT parameter to create a workload group similar to staticrc40.

SELECT Request_min_resource_grant_percent = Effective_request_min_resource_grant_percent
FROM sys.dm_workload_management_workload_groups_stats
WHERE name = 'staticrc40'

Note

工作负荷组基于总体系统资源百分比运行。Workload groups operate based on percentage of overall system resources.

由于工作负荷组基于总体系统资源百分比运行，因此，在纵向扩展和缩减的过程中，分配给静态资源类的、相对于总体系统资源的资源百分比会发生变化。Because workload groups operate based on percentage of overall system resources, as you scale up and down, the percentage of resources allocated to static resource classes relative to the overall system resources changes.例如，DW1000c 上的 staticrc40 会分配 9.6% 的总体系统资源。For example, staticrc40 at DW1000c allocates 9.6% of the overall system resources.在 DW2000c 上，则会分配 19.2%。At DW2000c, 19.2% are allocated.无论是纵向扩展以提高并发度，还是为每个请求分配更多的资源，此模型都是类似的。This model is similar if you wish to scale up for concurrency versus allocating more resources per request.

创建工作负荷组Create Workload Group

在 REQUEST_MIN_RESOURCE_GRANT_PERCENT 已知的情况下，可以使用 CREATE WORKLOAD GROUP 语法来创建工作负荷组。With the known REQUEST_MIN_RESOURCE_GRANT_PERCENT, you can use the CREATE WORKLOAD GROUP syntax to create the workload group.可以选择性地指定一个大于零的 MIN_PERCENTAGE_RESOURCE 以隔离工作负荷组的资源。You can optionally specify a MIN_PERCENTAGE_RESOURCE that is greater than zero to isolate resources for the workload group.此外，可以选择性地指定小于 100 的 CAP_PERCENTAGE_RESOURCE，以限制工作负荷组可以消耗的资源量。Also, you can optionally specify CAP_PERCENTAGE_RESOURCE less than 100 to limit the amount of resources the workload group can consume.

以下示例将 MIN_PERCENTAGE_RESOURCE 设置为提供 9.6% 的系统资源专供 wgDataLoads 使用，并保证一个查询始终都能够运行。The below example sets the MIN_PERCENTAGE_RESOURCE to dedicate 9.6% of the system resources to wgDataLoads and guarantees one query will be able to run all the times.此外，CAP_PERCENTAGE_RESOURCE 设置为 38.4%，并将此工作负荷组限制为四个并发请求。Additionally, CAP_PERCENTAGE_RESOURCE is set to 38.4% and limits this workload group to four concurrent requests.QUERY_EXECUTION_TIMEOUT_SEC 参数设置为 3600，这样，运行超过 1 小时的任何查询将自动取消。By setting the QUERY_EXECUTION_TIMEOUT_SEC parameter to 3600, any query that runs for more than 1 hour will be automatically canceled.

创建分类器Create the Classifier

以前，查询到资源类的映射是通过 sp_addrolemember 实现的。Previously, the mapping of queries to resource classes was done with sp_addrolemember.若要实现相同的功能并将请求映射到工作负荷组，请使用 CREATE WORKLOAD CLASSIFIER 语法。To achieve the same functionality and map requests to workload groups, use the CREATE WORKLOAD CLASSIFIER syntax.仅允许使用 sp_addrolemember 将资源映射到基于登录名的请求。Using sp_addrolemember only allowed you to map resources to a request based on a login.除了登录名以外，分类器还提供其他选项，例如：A classifier provides additional options besides login, such as:
- labellabel
- 会话session
- 时间。以下示例分配来自 AdfLogin 登录名的查询，这些查询的 OPTION LABEL 也设置为 factloads 到上面创建的工作负荷组 wgDataLoads 的映射。time The below example assigns queries from the AdfLogin login that also have the OPTION LABEL set to factloads to the workload group wgDataLoads created above.