Workload Management in Azure Synapse Analytics: Classification

Introduction

To better understanding of workload management concepts in Azure Synapse Analytics, I recommend reading my previous article titled "Workload Management Concepts in Azure Synapse Analytics." This article explores the significance of workload classification, the assignment of resource classes, and importance of requests, 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. A better use case in a typical data warehousing environment would be when you want to prioritize your data load by assigning them higher resource classes and resources compared to queries from users, which can be considered with a lower resource class than the data load. Not only this, but you can also assign finer controls by subclassifying both the query and data loads to ensure the larger data load or query has higher resource class or importance settings than the smaller ones. For example, the biometric data load in a company might be huge and need to be loaded before the payroll calculation data which is small.

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

How to create a workload classifier?

Workload classification is a function that can map the users with workload group that has been already defined.

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 = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH }])
[;]

SQL

Copy

Now let us look at each of these parameters in detail.

WORKLOAD_GROUP: The name which maps the request to a workload group at the time of creating the classifier.

MEMBERNAME: The security user account which can be a database user, role, or AAD login/AAD group.

WLM_LABEL: The label is like that of a tag that we use when creating any Azure services. It is an optional parameter, and one can use 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')

SQL

Copy

WLM_CONTEXT: This is an optional parameter. Instead of labeling each query in a session, you can simply apply session context, which can 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'

SQL

Copy

START_TIME and END_TIME: Both start and end times are in HH:MM format under the UTC zone. Both must be specified together, and it is a great feature to route the activity to be carried out in a specific time frame only.

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

SQL

Copy

IMPORTANCE:  It specifies the importance of a request that has been made, and it influences the order in which the requests are scheduled by assigning priority access to resources. There are 5 different types of importance that can be set, and NORMAL is the default one.

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

PARAMETER WEIGHTING: Weighting will be assigned to the requests based on their classification parameters, and the higher the weightage importance will be determined.

Classifier Parameter	Weight
USER	64
ROLE	32
WLM_LABEL	16
WLM_CONTEXT	8
START_TIME/END_TIME	4

For example, when our workload classifier fits with the following parameters, the higher the weightage of the classifier priority will be given to that workload classifier. The second one with WLM_CONTEXT & USER wins in this below example. Hence the proper mix of weightage should be declared if prioritization for the classifier 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');

   SQL

   Copy

Dropping Classifier

DROP WORKLOAD CLASSIFIER etlloads;

SQL

Copy

Example

An example of creating a classifier with staticrc20 workload group

CREATE WORKLOAD CLASSIFIER etlloads

  WITH (WORKLOAD_GROUP = 'staticrc20'

       ,MEMBERNAME = ‘etlrole’

      ,IMPORTANCE = above_normal);

SQL

Copy

Summary

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

Reference: Official Microsoft documentation.