Azure Synapse Analytics 工作负荷分类Azure Synapse Analytics workload classification

本文介绍了在 Azure Synapse 中使用 Synapse SQL 池为传入请求分配工作负荷组和重要性的工作负荷分类过程。This article explains the workload classification process of assigning a workload group and importance to incoming requests with Synapse SQL pools in Azure Synapse.

分类Classification

使用工作负荷管理分类可以通过分配资源类重要性对请求应用工作负荷策略。Workload management classification allows workload policies to be applied to requests through assigning resource classes and importance.

可通过多种方法来分类数据仓库工作负荷,而最简单且最常用的分类方法是加载和查询。While there are many ways to classify data warehousing workloads, the simplest and most common classification is load and query. 可以使用 insert、update 和 delete 语句加载数据。You load data with insert, update, and delete statements. 可以使用 select 查询数据。You query the data using selects. 数据仓库解决方案通常对加载活动使用工作负荷策略,例如,分配具有更多资源的更高资源类。A data warehousing solution will often have a workload policy for load activity, such as assigning a higher resource class with more resources. 可对查询应用不同的工作负荷策略,例如,分配比加载活动更低的重要性。A different workload policy could apply to queries, such as lower importance compared to load activities.

还可以将加载和查询工作负荷进一步分类。You can also subclassify your load and query workloads. 进一步分类能够更好地控制工作负荷。Subclassification gives you more control of your workloads. 例如,查询工作负荷可能包括多维数据集刷新、仪表板查询或即席查询。For example, query workloads can consist of cube refreshes, dashboard queries or ad-hoc queries. 可以使用不同的资源类或重要性设置将其中的每个查询工作负荷分类。You can classify each of these query workloads with different resource classes or importance settings. 加载活动也可以受益于进一步分类。Load can also benefit from subclassification. 可将大型转换分配到较大的资源类。Large transformations can be assigned to larger resource classes. 可以使用较高的重要性来确保先加载重要销售数据,再加载天气数据或社交数据馈送。Higher importance can be used to ensure key sales data is loader before weather data or a social data feed.

不会对所有语句分类,因为它们不需要会影响执行的资源或重要因素。Not all statements are classified as they do not require resources or need importance to influence execution. DBCC 命令、BEGIN、COMMIT 和 ROLLBACK TRANSACTION 语句不会进行分类。DBCC commands, BEGIN, COMMIT, and ROLLBACK TRANSACTION statements are not classified.

分类过程Classification process

目前,对 Azure Synapse 中的 Synapse SQL 池进行分类是通过使用 sp_addrolemember 将用户分配给具有相应资源类的角色来实现的。Classification for Synapse SQL pool in Azure Synapse is achieved today by assigning users to a role that has a corresponding resource class assigned to it using sp_addrolemember. 使用此功能时,将请求特征化,使之超出资源类登录范围的能力会受到限制。The ability to characterize requests beyond a login to a resource class is limited with this capability. 现在,可以通过 CREATE WORKLOAD CLASSIFIER 语法来利用更丰富的分类方法。A richer method for classification is now available with the CREATE WORKLOAD CLASSIFIER syntax. 使用此语法,Synapse SQL 池用户可以通过 workload_group 参数为请求分配重要性和系统资源数。With this syntax, Synapse SQL pool users can assign importance and how much system resources are assigned to a request via the workload_group parameter.

备注

分类是按每个请求评估的。Classification is evaluated on a per request basis. 可以不同的方式对单个会话中的多个请求进行分类。Multiple requests in a single session can be classified differently.

分类权重Classification weighting

在分类过程中,将使用权重来确定分配哪个工作负荷组。As part of the classification process, weighting is in place to determine which workload group is assigned. 权重如下所示:The weighting goes as follows:

分类器参数Classifier Parameter 权重Weight
MEMBERNAME:USERMEMBERNAME:USER 6464
MEMBERNAME:ROLEMEMBERNAME:ROLE 3232
WLM_LABELWLM_LABEL 1616
WLM_CONTEXTWLM_CONTEXT 88
START_TIME/END_TIMESTART_TIME/END_TIME 44

membername 参数是必需的。The membername parameter is mandatory. 但是,如果指定的 membername 是数据库用户而不是数据库角色,用户的权重更高,因此选择该分类器。However, if the membername specified is a database user instead of a database role, the weighting for user is higher and thus that classifier is chosen.

如果某个用户是多个角色的成员,并且这些角色分配有不同的资源类或者在多个分类器中相匹配,则会为该用户分配最高的资源类。If a user is a member of multiple roles with different resource classes assigned or matched in multiple classifiers, the user is given the highest resource class assignment. 此行为与现有的资源类分配行为保持一致。This behavior is consistent with existing resource class assignment behavior.

系统分类器System classifiers

工作负荷分类采用系统工作负荷分类器。Workload classification has system workload classifiers. 系统分类器将现有的资源类角色成员身份映射到具有一般重要性的资源类资源分配。The system classifiers map existing resource class role memberships to resource class resource allocations with normal importance. 无法删除系统分类器。System classifiers can't be dropped. 若要查看系统分类器,可运行以下查询:To view system classifiers, you can run the below query:

SELECT * FROM sys.workload_management_workload_classifiers where classifier_id <= 12

混合使用资源类分配和分类器Mixing resource class assignments with classifiers

使用自动创建的系统分类器能够轻松迁移到工作负荷分类。System classifiers created on your behalf provide an easy path to migrate to workload classification. 开始创建具有重要性的新分类器时,使用具有分类优先顺序的资源类角色映射可能会导致错误分类。Using resource class role mappings with classification precedence, can lead to misclassification as you start to create new classifiers with importance.

假设出现了下面这种情景:Consider the following scenario:

  • 某个现有的数据仓库包含已分配到 largerc 资源类角色的数据库用户 DBAUser。An existing data warehouse has a database user DBAUser assigned to the largerc resource class role. 资源类分配是使用 sp_addrolemember 进行的。The resource class assignment was done with sp_addrolemember.
  • 现已使用工作负荷管理更新该数据仓库。The data warehouse is now updated with workload management.
  • 为了测试新的分类语法,为数据库角色 DBARole(DBAUser 是其成员)创建了一个分类器(用于将用户映射到 mediumrc),并且该角色具有较高的重要性。To test the new classification syntax, the database role DBARole (which DBAUser is a member of), has a classifier created for them mapping them to mediumrc and high importance.
  • 当 DBAUser 登录并运行查询时,该查询将分配到 largerc,When DBAUser logs in and runs a query, the query will be assigned to largerc. 因为用户优先于角色成员身份。Because a user takes precedence over a role membership.

为了简化分类错误的排查,我们建议在创建工作负荷分类器时删除资源类角色映射。To simplify troubleshooting misclassification, we recommended you remove resource class role mappings as you create workload classifiers. 以下代码返回现有的资源类角色成员身份。The code below returns existing resource class role memberships. 针对相应资源类返回的每个成员名称运行 sp_droprolememberRun sp_droprolemember for each member name returned from the corresponding resource class.

SELECT  r.name AS [Resource Class]
,       m.name AS membername
FROM    sys.database_role_members rm
JOIN    sys.database_principals AS r ON rm.role_principal_id = r.principal_id
JOIN    sys.database_principals AS m ON rm.member_principal_id = m.principal_id
WHERE   r.name IN ('mediumrc','largerc','xlargerc','staticrc10','staticrc20','staticrc30','staticrc40','staticrc50','staticrc60','staticrc70','staticrc80');

--for each row returned run
sp_droprolemember '[Resource Class]', membername

后续步骤Next steps