How to Create a dedicated SQL pool workload classifier using the Azure portal

We can create a workload classifier for assigning queries to a workload group. The classifier will assign requests from the ELTLogin SQL user to the DataLoads workload group.

Table of Contents

Configure workload isolation

Dedicated SQL pool resources can be isolated and reserved for specific workloads by creating workload groups.The following steps create a workload group in mySampleDataWarehouse.

To create a workload group with 20 percent isolation:

Navigate to your mySampleDataWarehouse dedicated SQL pool page.
Select Workload management.
Select New workload group.
Select Custom

Enter DataLoads for the Workload group.
Enter 20 for Min. resources %.
Enter 5 for Min. resources % per request.
Enter 100 for Cap resources %.
Enter Save.

A portal notification appears when the workload group is created. The workload group resources are displayed in the charts below the configured values.

Create a workload classifier

Create a login for ELTLogin

CREATE LOGIN [ELTLogin] WITH PASSWORD='<strongpassword>'

Create user and grant permissions

CREATE USER [ELTLogin] FOR LOGIN [ELTLogin]
GRANT CONTROL ON DATABASE::mySampleDataWarehouse TO ELTLogin 
END
;

Configure workload classification

Classification allows you to route requests, based on a set of rules, to a workload group.
In Configure workload isolation , we created the DataLoads workload group. Now you will create a workload classifier to route queries to the DataLoads workload group.

Navigate to your mySampleDataWarehouse dedicated SQL pool page.
Select Workload management.

Select Settings & classifiers on the right-hand side of the DataLoads workload group.

Select Not configured under the Classifiers column.
Select + Add classifier.

Enter ELTLoginDataLoads for Name.
Enter ELTLogin for Member.
Choose High for Request Importance. Optional, normal importance is default.
Enter fact_loads for Label.
Select Add.
Select Save.

Verify and test classification

Check the sys.workload_management_workload_classifiers catalog view to verify existence of the ELTLoginDataLoads classifier.

SELECT * FROM sys.workload_management_workload_classifiers WHERE name = 'ELTLoginDataLoads'

the sys.workload_management_workload_classifier_details catalog view to verify classifier details.

SELECT c.[name], c.group_name, c.importance, cd.classifier_type, cd.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 = 'ELTLoginDataLoads'

Run the following statements to test classification. Ensure you are connected as ELTLogin and Label is used in query.

CREATE TABLE factstaging (ColA int)
INSERT INTO factstaging VALUES(0)
INSERT INTO factstaging VALUES(1)
INSERT INTO factstaging VALUES(2)
GO

CREATE TABLE testclassifierfact WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT * FROM factstaging
OPTION (LABEL='fact_loads')

Verify the CREATE TABLE statement classified to the DataLoads workload group using the ELTLoginDataLoads workload classifier.

SELECT TOP 1 request_id, classifier_name, group_name, resource_allocation_percentage, submit_time, [status], [label], command 
FROM sys.dm_pdw_exec_requests 
WHERE [label] = 'fact_loads'
ORDER BY submit_time DESC