Workload in a data warehouse refers to the operations necessary to support the data warehouse summarized by the following process:
- Loading data into the warehouse
- Data warehouse analysis and reporting
- Managing data
- Exporting data
How do we handle workload in Azure Synapse?
The query performance in Azure SQL Datawarehouse is managed using resource classes. A resource class is the allocation of resources limit, that governs the computing and concurrency of query execution. Administrators can configure resources by setting limits on computing resources assigned to each query and the number of queries running concurrently.
The downside to these resource classes is that once configured, we have no control over the workload. We cannot reserve the resources or ensure their availability for critical workloads.
This has been overcome with the introduction of Azure Synapse where workload management is handled with the following three concepts.
- Workload Classification
- Workload Isolation
- Workload Importance
Workload Classification
To classify the workload, first, create a workload group and set the level of importance to that group. Before Azure Synapse, the same was achieved with role membership to sp_addrolemember.
The same operation can be achieved with T_SQL in Azure Synapse.
Workload Isolation
Workload isolation is used to reserve resources exclusively for a workload group. By defining isolation, the resources will not be assigned to a shared pool. This ensures the availability of resources exclusively for the workload group.
We need to be cautious while configuring isolation since resources will be allocated even if there are no active requests in the workload group.
Workload Importance
We might have critical workloads in Data Warehouse that need to meet the SLA before other operations in Data Warehouse. This type of importance levels can be set in Azure Synapse with Workload importance. There are five levels of Workload importance
- low
- below_normal
- normal
- above_normal
- high
Let us see this in action with the Azure portal.
The first step would be to create a workload group. Access the Azure portal and navigate to the resource that we created for Azure Synapse Analytics and click on the workload management.
Add a new workload group and select save.
Click on the settings & classifiers to set the workload management attributes.
The workload importance can be set in the Settings.
The workload classifier can be added in the classifier tab.
In this way, Azure Synapse makes it easier to manage the workload with User Interface.
Learn more about Microsoft Power BI services offerings from Visual BI solutions here.
