Workload Management Concepts in Azure Synapse Analytics

Managing varied workloads with proper resource allocation for multiple concurrent user environments is a team's biggest challenge when retrieving data from an azure synapse analytics dedicated SQL pool DB. Workload management in azure synapse analytics gives you access to control your workload that are utilizing your system resources. Setting up the best workload management scheme is critical to effectively manage the available resources. Basically, workload management refers to all the operations that can be set to ensure no resource contention while accessing the data stored inside the data warehouse.

Workload Groups

Creating a workload group is the first step toward setting up the pillars of workload management. The workload group lets us determine and create a customized entity or group which can allow a specific number of resources get utilized based on the different properties that has been set. The three pillars are part of workload group which can help the administrator to have fine-grained access defined for various sets of users in an organization.

Pillars of Workload Management

Before the workload management group feature was in place, Synapse SQL used resource classes to manage query performance. Resource classes allowed users to assign memory based on role membership, but it behaves as a one-time activity, and you cannot have any control mechanism or can govern the workload running under the synapse database. For example, a user who has been granted a smallrc role membership can consume all the 100% memory available. With the new dedicated pool's workload management feature, three workload management concepts will help you control how your queries are utilizing the available system resources in a dedicated SQL pool.

 

Workload Classification

It is used to assign setting up the importance levels with the workload group. Previously this was done using the role membership assignment with 'sp_addrolemember', which will now be taken care of by workload classification using the 'create workload classifier' function. In addition to this, it also has options to set labels, start and end time, session as per the requirements.

Workload Importance

Specifies influence on the order of access for the resources to execute the queries. We must understand that workload importance is not a shortcut that would execute a query with high importance immediately canceling or ignoring every other requests; it just cuts down the wait time that would be required normally for a resource to acquire its share of resources. A typical example is when a resource with higher importance will have first access to resources available in the system that the ones with lower importance.

Workload Isolation

it reserves the resources exclusively for the workload group. It ensures that resources are continuously available for the execution of some important processes for the defined workload groups. If there is no workload isolation, all query requests will utilize the shared resource pool and contending for the resources. Once in the shared pool, the availability of resources for processes are not guaranteed and is allotted only based on the importance that has been set.

Summary

I hope this gave a basic understanding of how performance is configured for smooth running in the synapse dedicated pool. This is part one of the azure synapse workload management that I have started to write. I plan to cover all the performances related to synapse in this series in the coming weeks.

Reference: Official Microsoft documentation.