在 Azure Synapse Analytics 中管理和监视工作负荷重要性Manage and monitor workload importance in Azure Synapse Analytics

在 Azure Synapse 中使用 DMV 和目录视图管理和监视 Synapse SQL 请求级别重要性。Manage and monitor Synapse SQL request level importance in Azure Synapse using DMVs and catalog views.

监视重要性Monitor importance

使用 sys.dm_pdw_exec_requests 动态管理视图中新的“重要性”列监视重要性。Monitor importance using the new importance column in the sys.dm_pdw_exec_requests dynamic management view. 以下监视查询显示了查询的提交时间和开始时间。The below monitoring query shows submit time and start time for queries. 查看提交时间和开始时间以及重要性,以了解重要性如何影响计划。Review the submit time and start time along with importance to see how importance influenced scheduling.

SELECT s.login_name, r.status, r.importance, r.submit_time, r.start_time
  FROM sys.dm_pdw_exec_sessions s
  JOIN sys.dm_pdw_exec_requests r ON s.session_id = r.session_id
  WHERE r.resource_class is not null
ORDER BY r.start_time

若要进一步了解如何计划查询,请使用目录视图。To look further into how queries are being schedule, use the catalog views.

使用目录视图管理重要性Manage importance with catalog views

sys.workload_management_workload_classifiers 目录视图包含有关分类器的信息。The sys.workload_management_workload_classifiers catalog view contains information on classifiers. 若要排除映射到资源类的系统定义的分类器,请执行以下代码:To exclude the system-defined classifiers that map to resource classes execute the following code:

SELECT *
  FROM sys.workload_management_workload_classifiers
  WHERE classifier_id > 12

目录视图 sys.workload_management_workload_classifier_details 包含有关创建分类器时使用的参数的信息。The catalog view, sys.workload_management_workload_classifier_details, contains information on the parameters used in creation of the classifier. 以下查询显示 ExecReportsClassifier 是使用 membername 参数(值为 ExecutiveReports)创建的:The below query shows that ExecReportsClassifier was created on the membername parameter for values with ExecutiveReports:

SELECT c.name,cd.classifier_type, classifier_value
  FROM sys.workload_management_workload_classifiers c
  JOIN sys.workload_management_workload_classifier_details cd
    ON cd.classifier_id = c.classifier_id
  WHERE c.name = 'ExecReportsClassifier'

查询结果

为了简化分类错误的排查,我们建议在创建工作负荷分类器时删除资源类角色映射。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. 对从相应资源类返回的每个 membername 运行 sp_droprolemember。Run sp_droprolemember for each membername returned from the corresponding resource class. 以下是在删除工作负荷分类器之前检查是否存在的示例:Below is an example of checking for existence before dropping a workload classifier:

IF EXISTS (SELECT 1 FROM sys.workload_management_workload_classifiers WHERE name = 'ExecReportsClassifier')
  DROP WORKLOAD CLASSIFIER ExecReportsClassifier;
GO

后续步骤Next steps