Workload Management in Azure Synapse Analytics: Classification

Introduction

To get a better grasp of workload management concepts in Azure Synapse Analytics, I recommend reading my previous article titled “Workload Management Concepts in Azure Synapse Analytics.” In that article, we discussed the importance of workload classification, resource class assignment, request prioritization, and the prioritization of data load and queries. 

Classification in workload management

Classification in workload management allows us to configure policies that can be applied to incoming requests by assigning resource classes and importance. In a typical data warehousing environment, a good use case for classification is when you want to prioritize your data load by giving it a higher resource class and resources compared to user queries, which can be assigned a lower resource class. Additionally, you can also further subclassify both the queries and data loads to ensure that larger data loads or queries have higher resource classes or importance settings than smaller ones. For instance, in a company, the biometric data load may be massive and need to be loaded before the smaller payroll calculation data.

Note: DBCC commands like BEGIN, COMMIT, and ROLLBACK TRANSACTION statements cannot be classified.

How to create a workload classifier?

A workload classifier is a function that maps users to predefined workload groups.

CREATE WORKLOAD CLASSIFIER classifier_name 
WITH 
    (WORKLOAD_GROUP = 'name' 
    ,   MEMBERNAME = 'security_account'
[ [ , ] WLM_LABEL = 'label' ] 
[ [ , ] WLM_CONTEXT = 'context' ] 
[ [ , ] START_TIME = 'HH:MM' ] 
[ [ , ] END_TIME = 'HH:MM' ]   
[ [ , ] IMPORTANCE =  NORMAL ])
[;]

Let’s take a closer look at each of these parameters.

WORKLOAD_GROUP: This parameter maps the request to a workload group when creating the classifier.

MEMBERNAME: This parameter represents the security user account, which can be a database user, role, or AAD login/AAD group.

WLM_LABEL: The WLM_LABEL is optional and acts as a tag that can be used when creating any Azure service. It can be used with the OPTION(LABEL) in the request to match the classifier configuration.

CREATE WORKLOAD CLASSIFIER etlloads WITH 

( WORKLOAD_GROUP = 'dataload_wg' ,MEMBERNAME     = 'etlrole' ,WLM_LABEL      = 'fact_load' )

--execute the select query with OPTION LABEL

SELECT COUNT(*)
  FROM usertable
  OPTION (LABEL = 'fact_load')

WLM_CONTEXT: This is an optional parameter that allows you to apply session context instead of labeling each query in a session. The session context will last for the entire session.

CREATE WORKLOAD CLASSIFIER etlloads WITH 
( WORKLOAD_GROUP = 'dataload_wg',MEMBERNAME  = 'etlrole' ,WLM_CONTEXT      = 'fact_load_cont' )

--setting the session context
EXEC sys.sp_set_session_context @key = 'wlm_context', @value="fact_load_cont"

START_TIME and END_TIME: These parameters specify the start and end times in HH:MM format under the UTC zone. Both parameters must be specified together, allowing for activity to be carried out within a specific time frame only.

CREATE WORKLOAD CLASSIFIER etlloads WITH
( WORKLOAD_GROUP = ' dataload_wg'
 ,MEMBERNAME     = 'etlrole'
 ,START_TIME     = '22:00'
 ,END_TIME       = '02:00')

IMPORTANCE: The IMPORTANCE parameter specifies the importance of a request and influences the order in which requests are scheduled by assigning priority access to resources. There are five different importance levels: LOW, BELOW_NORMAL, NORMAL (default), ABOVE_NORMAL, and HIGH.

• LOW
• BELOW_NORMAL
• NORMAL (default)
• ABOVE_NORMAL
• HIGH

PARAMETER WEIGHTING: Requests are assigned weights based on their classification parameters, with a higher weight indicating greater importance.

For example, if a workload classifier fits with the following parameters, the classifier with the higher weightage will be given priority. In the example below, the second classifier with WLM_CONTEXT and USER wins. Therefore, it is important to declare the proper mix of weightage for the classifier if prioritization is expected.

1. START & END_TIME, WLM_LABEL & ROLE: 4+16+32= 52 Points
2. WLM_CONTEXT & USER: 8+64= 72 Points

   -- Use the master database
   USE master;
   
   -- Create login etlrole with password 'welcome@123'
   CREATE LOGIN etlrole WITH PASSWORD = 'welcome@123';
   
   -- Use the dedpooldb database
   USE dedpooldb;
   
   -- Create user etlrole from login etlrole
   CREATE USER etlrole FROM LOGIN etlrole;
   
   -- Create workload group
   CREATE WORKLOAD GROUP dataload_wg_new
   WITH
   (
       MIN_PERCENTAGE_RESOURCE = 26,
       REQUEST_MIN_RESOURCE_GRANT_PERCENT = 3.25,
       CAP_PERCENTAGE_RESOURCE = 100
   );
   
   -- Create workload classifier
   CREATE WORKLOAD CLASSIFIER etloads WITH
   (
       WORKLOAD_GROUP = 'dataload_wg',
       MEMBERNAME = 'etrole',
       WLM_LABEL = 'fact_load'
   );
   
   -- Select count from usertable
   SELECT COUNT(*) FROM usertable
   OPTION (LABEL = 'fact_load');
   

   

Dropping a Classifier

DROP WORKLOAD CLASSIFIER etlloads;

Example

An example of creating a classifier with the “staticrc20” workload group:

CREATE WORKLOAD CLASSIFIER etlloads

  WITH (WORKLOAD_GROUP = 'staticrc20'

       ,MEMBERNAME = ‘etlrole’

      ,IMPORTANCE = above_normal);

Summary

These are the practical steps for creating a classification after creating a workload group.

For more information, refer to the official Microsoft documentation.