Skip to content

sql-data-warehouse-how-to-manage-and-monitor-workload-importance.md

Latest commit

 

History

History
69 lines (52 loc) · 3.58 KB

sql-data-warehouse-how-to-manage-and-monitor-workload-importance.md

File metadata and controls

69 lines (52 loc) · 3.58 KB
title Manage and monitor workload importance in dedicated SQL pool
description Learn how to manage and monitor request level importance dedicated SQL pool for Azure Synapse Analytics.
author ajagadish-24
ms.author ajagadish
ms.date 02/04/2020
ms.service azure-synapse-analytics
ms.subservice sql-dw
ms.topic how-to
ms.custom azure-synapse

Manage and monitor workload importance in dedicated SQL pool for Azure Synapse Analytics

Manage and monitor dedicated SQL pool request level importance in Azure Synapse using DMVs and catalog views.

Monitor importance

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

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

The catalog view, sys.workload_management_workload_classifier_details, contains information on the parameters used in creation of the classifier. 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'

query results

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. 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

[!div class="nextstepaction"] Go to Configure Workload Importance