Workload management for cloud data warehouses is one of the most important administrative task for DBAs/Data Engineers (depends who manage the data warehouse). With cloud offerings fully or partially automating many other administrative tasks, the workload management becomes one of the few tasks that need special attention.
Also for customers who are trying Azure Synapse for the first time and got disappointed because of the performance is not as expected. In my experience, the main reason is not configuring workload management.
If I got your attention let’s dive in 🙂 The discussion here will focus only on one part of Azure Synapse which is the Dedicated SQL Pools
Why workload management?
Two reasons; one for Synapse as a product and one for you as an administrator of a powerful data warehouse engine.
Synapse as a product:
To maintain the performance SLA, the best way to have a predicted and transparent policy is to apply workload management so the customer knows how many queries can run concurrently and how much resources would be allocated to each.
You as administrator:
The consumers of your data warehouse are not all the same. You will have queries that are of highest importance compared to others. You will have queries that require big portion of the engine’s resources otherwise you will get very bad performance. You might need to limit the max execution time for certain queries to a time limit so you won’t have any one hogging the resources for long time and limit the accessibility to the resources for other users.
How to enable workload management?
No you don’t need to do anything. workload management is ON BY DEFAULT. That’s very important to stress on because by default all the queries will be classified as a small resource class which means they have a small friction of the total resources. Notably to mention here that Synapse doesn’t use CPU & Memory as unit however it uses a new dedicated scaling unit called DWU your choice of how many DWU dictate not only how much resources but how many max concurrent quires.
Max concurrency & Resource percentage
The memory & concurrency limits per DWU docs is very detailed and important to understand the max limits. I’ll take a small sized (300 DWU) as an example.
300 DWU gives you 180 GB memory and one compute node. Without any configurations, any query by default will be classified as smallrc (small resource class) and the max number of these queries is 12. That’s because in the 300 DWU the max resources allocated per query is 8% which means your query will not perform well if you need more resources which is inevitable in the case of data warehouses.
This video shows a quick demo to explain it.
So since workload management is ON by default, how do we configure it?
Old way (aka don’t use it)
In the first generation of Synapse (aka SQL Data Warehouse) there are pre-defined resource classes implemented as database roles and by adding a database principal to a role using sp_addrolemember system stored procedure.
The main draw back of this way of course that you can’t create custom resource classes (think resource governor in SQL Server) and there’s no way to quickly classify query A to be matched with different class than query B if they are sent by the same database principal.
Workload Groups & Classifiers
I won’t repeat the documentation but I’ll approach it from a different prospective. While the docs discuss this topic from Workload Classification, Workload Importance, and Workload Isolation angels, this gives you good idea about the capabilities but not understanding of how so here’s my approach for it.
There are two objects that you need to create and configure
Workload group:
In workload group, you can choose
- The minimum and cap (absolute max) resources percentage. The total min of all workload groups on the system can’t exceed 100%.
- Query timeout to stop the long running queries from holding resources for long time.
- Minimum & max resource grant per request. Resource grant is the minimum amount available to have the query starting executing.
If you set the min resource percentage to 30% and min resource grant per request to be 10% then you guarantee 3 queries to be running on the system. There’s however adjustment for the max concurrent queries
Workload Classifier:
The main purpose of the classifier is to match a condition or a set of conditions to a workload group.
Note: matching is done on all conditions. in other meaning consider it AND if statement. All the conditions has to be true for the match to happen
That’s why it’s typical to have multiple classifiers to the same workload group so you can match either of them.
The conditions are:
A principal: if user equal to the principal in the classifier or a member of a role in the classifier then it’s a match
a label: A label is used in
SELECTstatements usingOPTION(LABEL='any_string')any_string in this case is the labelSession Context: like a label but it applies on all quires in a session. That applies to any statement not just
SELECTstatements. You enable the context by assigning any value to the wlm_context
EXEC sys.sp_set_session_context @key = 'wlm_context', @value = 'big_context'
In this example, big_context is the string to be matched.
Dynamically match any user :
To make a dynamic matching where any user can change their assigned workload group to a different one is pretty easy in this model. The workload classification happened on query by query bases so with every query, there’s a new matching.
It’s time to use the old wildcard trick of the public database role. All users are members in the public role which makes it the best to wildcard matches like this
--dynamic using label
CREATE WORKLOAD CLASSIFIER myBigQueriesClassifier2
WITH (WORKLOAD_GROUP = 'largeWorkloadGroup'
,MEMBERNAME = 'public'
,WLM_LABEL='big_label' )
Having this classifier will cause any user to be matched to the largeWorkloadGroup if using OPTION(LABEL='big_label')
Similarly you can combine this classifier with another classifier
--dynamic using session context
CREATE WORKLOAD CLASSIFIER myBigQueriesClassifier3
WITH (WORKLOAD_GROUP = 'largeWorkloadGroup',MEMBERNAME = 'public'
,WLM_CONTEXT='big_context')
Remember, don’t use both wlm_context & wlm_label if you want the matching happened either context or label present. Keep them in two classifiers pointing to the same workload group.
Sample notebook with all the code located here
References:
| [Workload management – Azure Synapse Analytics | Microsoft Docs](https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-workload-management) |
| [CREATE WORKLOAD GROUP (Transact-SQL) – SQL Server | Microsoft Docs](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-workload-group-transact-sql?toc=/azure/synapse-analytics/sql-data-warehouse/toc.json&bc=/azure/synapse-analytics/sql-data-warehouse/breadcrumb/toc.json&view=azure-sqldw-latest&preserve-view=true) |
| [CREATE WORKLOAD Classifier (Transact-SQL) – SQL Server | Microsoft Docs](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-workload-classifier-transact-sql?toc=/azure/synapse-analytics/sql-data-warehouse/toc.json&bc=/azure/synapse-analytics/sql-data-warehouse/breadcrumb/toc.json&view=azure-sqldw-latest&preserve-view=true) |
