The short answer
In a busy analytical data warehouse, long-running data science queries and batch pipelines can consume all available execution slots, leaving executive dashboards stuck in waiting queues. Synapse Workload Management allows you to resources and assign priorities.
Workload Groups
A Workload Group defines a boundary for resource consumption. You configure:
MIN_PERCENTAGE_RESOURCE: Guaranteed percentage of cluster resources reserved exclusively for this group.CAP_PERCENTAGE_RESOURCE: Hard ceiling on resources this group can consume.REQUEST_MIN_RESOURCE_GRANT_PERCENT: Minimum resources allocated to an individual query.
CREATE WORKLOAD GROUP DataEngineeringGroup
WITH (
MIN_PERCENTAGE_RESOURCE = 20,
CAP_PERCENTAGE_RESOURCE = 50,
REQUEST_MIN_RESOURCE_GRANT_PERCENT = 5
);
CREATE WORKLOAD GROUP ExecutiveDashboardGroup
WITH (
MIN_PERCENTAGE_RESOURCE = 50,
CAP_PERCENTAGE_RESOURCE = 100,
REQUEST_MIN_RESOURCE_GRANT_PERCENT = 2
);Workload Classifiers
Workload Classifiers map incoming user sessions or database roles to specific Workload Groups:
CREATE WORKLOAD CLASSIFIER ClassifyADF
JOIN DataEngineeringGroup
WITH (
MEMBERNAME = 'adf_pipeline_sp',
IMPORTANCE = BELOW_NORMAL
);
CREATE WORKLOAD CLASSIFIER ClassifyPowerBI
JOIN ExecutiveDashboardGroup
WITH (
MEMBERNAME = 'pbi_exec_role',
IMPORTANCE = HIGH
);When an analyst opens an executive Power BI report, the query is assigned to the ExecutiveDashboardGroup with HIGH importance. Even if an ETL pipeline is loading 100 million rows, the dashboard query preempts lower-priority work and runs immediately.
Practical tuning guidelines
A common beginner mistake is setting the CAP_PERCENTAGE_RESOURCE to 100% on every workload group. If two unconstrained groups run large queries simultaneously, they will compete aggressively for memory, causing intermediate queries to spill to tempdb. Always set caps on batch ETL and data science queues so that interactive reporting groups retain guaranteed headroom.