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

工作负荷组提供隔离和包含系统资源的机制。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.

备注

有关同时使用工作负荷组和资源类的指南,请参阅工作负荷分类概念文档中的将资源类分配和分类器混合使用部分。See the Mixing resource class assignments with classifiers section in the Workload Classification concept document for guidance on using workload groups and resource classes at the same time.

了解现有的资源类配置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'

备注

工作负荷组基于总体系统资源百分比运行。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 会分配到 19.2% 的总体系统资源。For example, staticrc40 at DW1000c allocates 19.2% of the overall system resources. 在 DW2000c,则会分配 9.6%。At DW2000c, 9.6% 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.

下面的代码使用 mediumrc 作为一个示例的基础,将 MIN_PERCENTAGE_RESOURCE 设置为提供 10% 的系统资源专供 wgDataLoads 使用,并保证一个查询始终都能够运行。Using mediumrc as a basis for an example, the below code sets the MIN_PERCENTAGE_RESOURCE to dedicate 10% of the system resources to wgDataLoads and guarantees one query will be able to run all the times. 此外,CAP_PERCENTAGE_RESOURCE 设置为 40%,并将此工作负载组限制为四个并发请求。Additionally, CAP_PERCENTAGE_RESOURCE is set to 40% 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 WORKLOAD GROUP wgDataLoads WITH  
( REQUEST_MIN_RESOURCE_GRANT_PERCENT = 10
 ,MIN_PERCENTAGE_RESOURCE = 10
 ,CAP_PERCENTAGE_RESOURCE = 40
 ,QUERY_EXECUTION_TIMEOUT_SEC = 3600)

创建分类器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)分配到上面创建的工作负荷组 wgDataLoadstime 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.

CREATE WORKLOAD CLASSIFIER wcDataLoads WITH  
( WORKLOAD_GROUP = 'wgDataLoads'
 ,MEMBERNAME = 'AdfLogin'
 ,WLM_LABEL = 'factloads')

使用示例查询进行测试Test with a sample query

下面是一个示例查询和 DMV 查询,可用于确保正确配置工作负荷组与分类器。Below is a sample query and a DMV query to ensure the workload group and classifier are configured correctly.

SELECT SUSER_SNAME() --should be 'AdfLogin'

--change to a valid table AdfLogin has access to
SELECT TOP 10 *
  FROM nation
  OPTION (label='factloads')

SELECT request_id, [label], classifier_name, group_name, command
  FROM sys.dm_pdw_exec_requests
  WHERE [label] = 'factloads'
  ORDER BY submit_time DESC

后续步骤Next steps