Azure Synapse 分析工作负荷组隔离Azure Synapse Analytics workload group isolation

本文介绍了如何使用工作负荷组来配置工作负荷隔离、包含资源和应用用于执行查询的运行时规则。This article explains how workload groups can be used to configure workload isolation, contain resources, and apply runtime rules for query execution.

工作负荷组Workload groups

工作负荷组是一组请求的容器,是在系统上配置工作负荷管理(包括工作负荷隔离)的基础。Workload groups are containers for a set of requests and are the basis for how workload management, including workload isolation, is configured on a system. 使用 CREATE WORKLOAD GROUP 语法创建工作负荷组。Workload groups are created using the CREATE WORKLOAD GROUP syntax. 一个简单的工作负荷管理配置可以管理数据负荷和用户查询。A simple workload management configuration can manage data loads and user queries. 例如,一个名为 wgDataLoads 的工作负荷组将为加载到系统中的数据定义工作负荷方面。For example, a workload group named wgDataLoads will define workload aspects for data being loaded into the system. 此外,名为 wgUserQueries 的工作负荷组将为运行查询以从系统读取数据的用户定义工作负荷方面。Also, a workload group named wgUserQueries will define workload aspects for users running queries to read data from the system.

以下各部分将重点介绍工作负荷组如何提供定义隔离、包含、请求资源定义以及遵守执行规则的功能。The following sections will highlight how workload groups provide the ability to define isolation, containment, request resource definition, and adhere to execution rules.

工作负荷隔离Workload isolation

工作负荷隔离意味着专门为工作负荷组保留资源。Workload isolation means resources are reserved, exclusively, for a workload group. 通过在 CREATE WORKLOAD GROUP 语法中将 MIN_PERCENTAGE_RESOURCE 参数配置为大于零的值,可以实现工作负荷隔离。Workload isolation is achieved by configuring the MIN_PERCENTAGE_RESOURCE parameter to greater than zero in the CREATE WORKLOAD GROUP syntax. 对于需要遵守严格 SLA 的连续执行工作负荷,隔离可确保资源始终可用于工作负荷组。For continuous execution workloads that need to adhere to tight SLAs, isolation ensures resources are always available for the workload group.

配置工作负荷隔离隐式定义了一个有保证的并发级别。Configuring workload isolation implicitly defines a guaranteed level of concurrency. 例如,将 MIN_PERCENTAGE_RESOURCE 设置为 30%,将 REQUEST_MIN_RESOURCE_GRANT_PERCENT 设置为 2% 的工作负荷组可以保证 15 个并发。For example, a workload group with a MIN_PERCENTAGE_RESOURCE set to 30% and REQUEST_MIN_RESOURCE_GRANT_PERCENT set to 2% is guaranteed 15 concurrency. 由于工作负荷组中始终会保留 15-2% 的资源槽(无论 REQUEST_*MAX*_RESOURCE_GRANT_PERCENT 配置如何),所以并发级别是有保证的。The level of concurrency is guaranteed because 15-2% slots of resources are reserved within the workload group at all times (regardless of how REQUEST_*MAX*_RESOURCE_GRANT_PERCENT is configured). 如果 REQUEST_MAX_RESOURCE_GRANT_PERCENT 大于 REQUEST_MIN_RESOURCE_GRANT_PERCENT,且 CAP_PERCENTAGE_RESOURCE 大于 MIN_PERCENTAGE_RESOURCE,则每个请求会增加额外的资源。If REQUEST_MAX_RESOURCE_GRANT_PERCENT is greater than REQUEST_MIN_RESOURCE_GRANT_PERCENT and CAP_PERCENTAGE_RESOURCE is greater than MIN_PERCENTAGE_RESOURCE additional resources are added per request. 如果 REQUEST_MAX_RESOURCE_GRANT_PERCENTREQUEST_MIN_RESOURCE_GRANT_PERCENT 相等,且 CAP_PERCENTAGE_RESOURCE 大于 MIN_PERCENTAGE_RESOURCE,则可以进行额外的并发。If REQUEST_MAX_RESOURCE_GRANT_PERCENT and REQUEST_MIN_RESOURCE_GRANT_PERCENT are equal and CAP_PERCENTAGE_RESOURCE is greater than MIN_PERCENTAGE_RESOURCE, additional concurrency is possible. 请考虑以下方法以便确定有保证的并发:Consider the below method for determining guaranteed concurrency:

[有保证的并发] = [MIN_PERCENTAGE_RESOURCE] / [REQUEST_MIN_RESOURCE_GRANT_PERCENT][Guaranteed Concurrency] = [MIN_PERCENTAGE_RESOURCE] / [REQUEST_MIN_RESOURCE_GRANT_PERCENT]

备注

Min_percentage_resource 有特定的服务级别最小可行值。There are specific service level minimum viable values for min_percentage_resource. 有关详细信息,请参阅有效值For more information, see Effective Values for further details.

如果没有工作负荷隔离,请求在资源的共享池中操作。In the absence of workload isolation, requests operate in the shared pool of resources. 对共享池中资源的访问权限不能得到保证,按重要性分配。Access to resources in the shared pool is not guaranteed and is assigned on an importance basis.

在配置工作负荷隔离时应谨慎行事,因为即使工作负荷组中没有活动请求,也会将资源分配给工作负荷组。Configuring workload isolation should be done with caution as the resources are allocated to the workload group even if there are no active requests in the workload group. 过度配置隔离会导致系统整体利用率下降。Over-configuring isolation can lead to diminished overall system utilization.

用户在配置工作负荷管理解决方案时应避免 100% 工作负荷隔离:当在所有工作负荷组中配置的 min_percentage_resource 总和等于 100% 时,就可以实现 100% 的隔离。Users should avoid a workload management solution that configures 100% workload isolation: 100% isolation is achieved when the sum of min_percentage_resource configured across all workload groups equals 100%. 这种类型的配置过于严格和死板,几乎没有空间处理意外错误分类的资源请求。This type of configuration is overly restrictive and rigid, leaving little room for resource requests that are accidentally mis-classified. 提供一个预配以从未配置隔离的工作负荷组中执行一个请求。There is a provision to allow one request to execute from workload groups not configured for isolation. 分配给此请求的资源将在系统 DMV 中显示为零,并从系统保留资源中借用较小级别的资源授权。The resources allocated to this request will show up as a zero in the systems DMVs and borrow a smallrc level of resource grant from system reserved resources.

备注

为了确保最佳资源利用率,可以考虑采用这样一种工作负荷管理解决方案,该解决方案利用一些隔离来确保满足 SLA,并与基于工作负荷重要性进行访问的共享资源混合在一起。To ensure optimal resource utilization, consider a workload management solution that leverages some isolation to ensure SLAs are met and mixed with shared resources that are accessed based on workload importance.

工作负荷限制Workload containment

工作负荷限制指的是限制工作负荷组可以消耗的资源量。Workload containment refers to limiting the amount of resources a workload group can consume. 通过在 CREATE WORKLOAD GROUP 语法中将 CAP_PERCENTAGE_RESOURCE 参数配置为小于 100,可以实现工作负荷限制。Workload containment is achieved by configuring the CAP_PERCENTAGE_RESOURCE parameter to less than 100 in the CREATE WORKLOAD GROUP syntax. 考虑这样一个场景:用户需要系统的读取访问权限,以便他们能够通过即席查询运行 what-if 分析。Consider the scenario whereby users need read access to the system so they can run a what-if analysis via ad-hoc queries. 这些类型的请求可能会对系统上正在运行的其他工作负荷产生负面影响。These types of requests could have a negative impact on other workloads that are running on the system. 配置工作负荷限制可确保使用有限的资源。Configuring containment ensures the amount of resources is limited.

配置工作负荷限制隐式定义了最大的并发级别。Configuring workload containment implicitly defines a maximum level of concurrency. 将 CAP_PERCENTAGE_RESOURCE 设置为 60%,并将 REQUEST_MIN_RESOURCE_GRANT_PERCENT 设置为 1%,则工作负荷组最多允许 60 并发级别。With a CAP_PERCENTAGE_RESOURCE set to 60% and a REQUEST_MIN_RESOURCE_GRANT_PERCENT set to 1%, up to a 60-concurrency level is allowed for the workload group. 请考虑下面包含的方法来确定最大并发:Consider the method included below for determining the maximum concurrency:

[最大并发] = [CAP_PERCENTAGE_RESOURCE] / [REQUEST_MIN_RESOURCE_GRANT_PERCENT][Max Concurrency] = [CAP_PERCENTAGE_RESOURCE] / [REQUEST_MIN_RESOURCE_GRANT_PERCENT]

备注

当创建的工作负荷组的 MIN_PERCENTAGE_RESOURCE 的级别大于零时,工作负荷组的有效 CAP_PERCENTAGE_RESOURCE 将不会达到 100%。The effective CAP_PERCENTAGE_RESOURCE of a workload group will not reach 100% when workload groups with MIN_PERCENTAGE_RESOURCE at a level greater than zero are created. 有关有效运行时值,请参阅 sys.dm_workload_management_workload_groups_statsSee sys.dm_workload_management_workload_groups_stats for effective runtime values.

每个请求定义的资源Resources per request definition

工作负荷组提供了一种机制,用 CREATE WORKLOAD GROUP 语法中的 REQUEST_MIN_RESOURCE_GRANT_PERCENT 和 REQUEST_MAX_RESOURCE_GRANT_PERCENT 参数来定义每个请求分配的最小和最大资源量。Workload groups provide a mechanism to define the min and max amount of resources that are allocated per request with the REQUEST_MIN_RESOURCE_GRANT_PERCENT and REQUEST_MAX_RESOURCE_GRANT_PERCENT parameters in the CREATE WORKLOAD GROUP syntax. 在这种情况下,资源是指 CPU 和内存。Resources in this case are CPU and memory. 配置这些值决定了系统上可以实现多少资源和什么级别的并发。Configuring these values dictates how much resources and what level of concurrency can be achieved on the system.

备注

REQUEST_MAX_RESOURCE_GRANT_PERCENT 是一个可选参数,默认是为 REQUEST_MIN_RESOURCE_GRANT_PERCENT 指定的相同值。REQUEST_MAX_RESOURCE_GRANT_PERCENT is an optional parameter that defaults to the same value that is specified for REQUEST_MIN_RESOURCE_GRANT_PERCENT.

和选择资源类一样,配置 REQUEST_MIN_RESOURCE_GRANT_PERCENT 会设置请求所使用的资源值。Like choosing a resource class, configuring REQUEST_MIN_RESOURCE_GRANT_PERCENT sets the value for the resources utilized by a request. 在开始执行请求之前,确保向请求分配由设定值指示的资源量。The amount of resources indicated by the set value is guaranteed for allocation to the request before it begins execution. 对于从资源类迁移到工作负荷组的客户,可以考虑按照操作说明文章中的方法,首先将资源类映射到工作负荷组。For customers migrating from resource classes to workload groups, consider following the How To article to map from resources classes to workload groups as a starting point.

将 REQUEST_MAX_RESOURCE_GRANT_PERCENT 配置为大于 REQUEST_MIN_RESOURCE_GRANT_PERCENT 的值,可以让系统为每个请求分配更多的资源。Configuring REQUEST_MAX_RESOURCE_GRANT_PERCENT to a value greater than REQUEST_MIN_RESOURCE_GRANT_PERCENT allows the system to allocate more resources per request. 在计划请求时,系统根据共享池中的资源可用性和系统当前的负荷情况,确定实际向请求分配的资源,该资源分配值介于 REQUEST_MIN_RESOURCE_GRANT_PERCENT 和 REQUEST_MAX_RESOURCE_GRANT_PERCENT 之间。While scheduling a request, system determines actual resource allocation to the request, which is between REQUEST_MIN_RESOURCE_GRANT_PERCENT and REQUEST_MAX_RESOURCE_GRANT_PERCENT, based on resource availability in shared pool and current load on the system. 计划查询时,资源必须存在于资源的共享池中。The resources must exist in the shared pool of resources when the query is scheduled.

备注

REQUEST_MIN_RESOURCE_GRANT_PERCENT 和 REQUEST_MAX_RESOURCE_GRANT_PERCENT 的有效值取决于有效的 MIN_PERCENTAGE_RESOURCE 和 CAP_PERCENTAGE_RESOURCE 值。REQUEST_MIN_RESOURCE_GRANT_PERCENT and REQUEST_MAX_RESOURCE_GRANT_PERCENT have effective values that are dependent on the effective MIN_PERCENTAGE_RESOURCE and CAP_PERCENTAGE_RESOURCE values. 有关有效运行时值,请参阅 sys.dm_workload_management_workload_groups_statsSee sys.dm_workload_management_workload_groups_stats for effective runtime values.

执行规则Execution Rules

在即席报告系统上,客户可能会不小心执行失控查询,严重影响他人的工作效率。On ad-hoc reporting systems, customers can accidentally execute runaway queries that severely impact the productivity of others. 系统管理员不得不花时间终止失控查询,以释放系统资源。System admins are forced to spend time killing runaway queries to free up system resources. 工作负荷组提供了配置查询执行超时规则的功能,以取消超过指定值的查询。Workload groups offer the ability to configure a query execution timeout rule to cancel queries that have exceeded the specified value. 该规则可通过设置 CREATE WORKLOAD GROUP 语法中的 QUERY_EXECUTION_TIMEOUT_SEC 参数来配置。The rule is configured by setting the QUERY_EXECUTION_TIMEOUT_SEC parameter in the CREATE WORKLOAD GROUP syntax.

共享池资源Shared pool resources

共享池资源是未配置隔离的资源。Shared pool resources are the resources not configured for isolation. 将 MIN_PERCENTAGE_RESOURCE 设置为零的工作负荷组利用共享池中的资源来执行请求。Workload groups with a MIN_PERCENTAGE_RESOURCE set to zero leverage resources in the shared pool to execute requests. CAP_PERCENTAGE_RESOURCE 大于 MIN_PERCENTAGE_RESOURCE 的工作负荷组也会使用共享资源。Workload groups with a CAP_PERCENTAGE_RESOURCE greater than MIN_PERCENTAGE_RESOURCE also used shared resources. 共享池中可用的资源量按如下方式计算。The amount of resources available in the shared pool is calculated as follows.

[共享池] = 100 - [所有工作负荷组中 MIN_PERCENTAGE_RESOURCE 的总和][Shared Pool] = 100 - [sum of MIN_PERCENTAGE_RESOURCE across all workload groups]

对共享池中资源的访问权限会按重要性分配。Access to resources in the shared pool is allocated on an importance basis. 具有相同重要性级别的请求将按先进先出的方式访问共享池资源。Requests with the same importance level will access shared pool resources on a first in/first out basis.

后续步骤Next steps