Azure

How to Resolve Resource Class and Workload Management Issues in Synapse

Understanding Synapse SQL Resource Classes

Azure Synapse Analytics (dedicated SQL pool) uses resource classes to manage memory allocation and concurrency for queries. Misconfigured resource classes cause queries to queue indefinitely, run out of memory, or consume disproportionate resources. This guide covers resource class management and the newer workload management features.

Understanding the Root Cause

Resolving Resource Class and Workload Management Issues in Synapse requires more than applying a quick fix to suppress error messages. The underlying cause typically involves a mismatch between your application’s expectations and the service’s actual behavior or limits. Azure services enforce quotas, rate limits, and configuration constraints that are documented but often overlooked during initial development when traffic volumes are low and edge cases are rare.

When this issue appears in production, it usually indicates that the system has crossed a threshold that was not accounted for during capacity planning. This could be a throughput limit, a connection pool ceiling, a timeout boundary, or a resource quota. The error messages from Azure services are designed to be actionable, but they sometimes point to symptoms rather than the root cause. For example, a timeout error might actually be caused by a DNS resolution delay, a TLS handshake failure, or a downstream dependency that is itself throttled.

The resolution strategies in this guide are organized from least invasive to most invasive. Start with configuration adjustments that do not require code changes or redeployment. If those are insufficient, proceed to application-level changes such as retry policies, connection management, and request patterns. Only escalate to architectural changes like partitioning, sharding, or service tier upgrades when the simpler approaches cannot meet your requirements.

Impact Assessment

Before implementing any resolution, assess the blast radius of the current issue. Determine how many users, transactions, or dependent services are affected. Check whether the issue is intermittent or persistent, as this distinction changes the urgency and approach. Intermittent issues often indicate resource contention or throttling near a limit, while persistent failures typically point to misconfiguration or a hard limit being exceeded.

Review your Service Level Objectives (SLOs) to understand the business impact. If your composite SLA depends on this service’s availability, calculate the actual downtime or degradation window. This information is critical for incident prioritization and for justifying the engineering investment required for a permanent fix versus a temporary workaround.

Consider the cascading effects on downstream services and consumers. When Resource Class and Workload Management Issues in Synapse degrades, every service that depends on it may also experience failures or increased latency. Map out your service dependency graph to understand the full impact scope and prioritize the resolution accordingly.

Resource Class Types

Static Resource Classes

Resource Class Memory per Distribution (DW1000c) Max Concurrency
staticrc10 400 MB 40
staticrc20 800 MB 20
staticrc30 1,600 MB 16
staticrc40 3,200 MB 8
staticrc50 6,400 MB 6
staticrc60 12,800 MB 4
staticrc70 25,600 MB 2
staticrc80 51,200 MB 2

Dynamic Resource Classes

Resource Class Memory % (DW1000c) Max Concurrency
smallrc 6.25% 32
mediumrc 12.5% 16
largerc 25% 8
xlargerc 50% 4

Assigning Resource Classes

-- Assign a user to a resource class
EXEC sp_addrolemember 'largerc', 'etl_user';

-- Remove a user from a resource class
EXEC sp_droprolemember 'largerc', 'etl_user';

-- Check current resource class assignments
SELECT 
    m.name AS MemberName,
    r.name AS RoleName
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE r.name IN ('smallrc','mediumrc','largerc','xlargerc',
                  'staticrc10','staticrc20','staticrc30','staticrc40',
                  'staticrc50','staticrc60','staticrc70','staticrc80');

-- Check what resource class your current session is using
SELECT name FROM sys.database_principals 
WHERE principal_id = (
    SELECT role_principal_id FROM sys.database_role_members 
    WHERE member_principal_id = DATABASE_PRINCIPAL_ID()
    AND role_principal_id IN (
        SELECT principal_id FROM sys.database_principals 
        WHERE name LIKE '%rc%'
    )
);

Diagnosing Queued Queries

-- Find queries waiting in the queue
SELECT 
    r.request_id,
    r.status,
    r.submit_time,
    r.start_time,
    r.resource_class,
    r.importance,
    r.command
FROM sys.dm_pdw_exec_requests r
WHERE r.status = 'Queued'
ORDER BY r.submit_time;

-- Check active sessions and their resource class
SELECT 
    s.session_id,
    s.login_name,
    r.resource_class,
    r.status,
    r.submit_time,
    DATEDIFF(minute, r.submit_time, GETDATE()) AS wait_minutes,
    r.command
FROM sys.dm_pdw_exec_sessions s
JOIN sys.dm_pdw_exec_requests r ON s.session_id = r.session_id
WHERE r.status IN ('Running', 'Queued')
ORDER BY r.submit_time;

-- Check concurrency slot usage
SELECT 
    r.resource_class,
    COUNT(*) AS running_queries,
    SUM(r.resource_allocation_percentage) AS total_memory_pct
FROM sys.dm_pdw_exec_requests r
WHERE r.status = 'Running'
GROUP BY r.resource_class;

Resilience Patterns for Long-Term Prevention

Once you resolve the immediate issue, invest in resilience patterns that prevent recurrence. Azure’s cloud-native services provide building blocks for resilient architectures, but you must deliberately design your application to use them effectively.

Retry with Exponential Backoff: Transient failures are expected in distributed systems. Your application should automatically retry failed operations with increasing delays between attempts. The Azure SDK client libraries implement retry policies by default, but you may need to tune the parameters for your specific workload. Set maximum retry counts to prevent infinite retry loops, and implement jitter (randomized delay) to prevent thundering herd problems when many clients retry simultaneously.

Circuit Breaker Pattern: When a dependency consistently fails, continuing to send requests increases load on an already stressed service and delays recovery. Implement circuit breakers that stop forwarding requests after a configurable failure threshold, wait for a cooldown period, then tentatively send a single test request. If the test succeeds, the circuit closes and normal traffic resumes. If it fails, the circuit remains open. Azure API Management provides a built-in circuit breaker policy for backend services.

Bulkhead Isolation: Separate critical and non-critical workloads into different resource instances, connection pools, or service tiers. If a batch processing job triggers throttling or resource exhaustion, it should not impact the real-time API serving interactive users. Use separate Azure resource instances for workloads with different priority levels and different failure tolerance thresholds.

Queue-Based Load Leveling: When the incoming request rate exceeds what the backend can handle, use a message queue (Azure Service Bus or Azure Queue Storage) to absorb the burst. Workers process messages from the queue at the backend’s sustainable rate. This pattern is particularly effective for resolving throughput-related issues because it decouples the rate at which requests arrive from the rate at which they are processed.

Cache-Aside Pattern: For read-heavy workloads, cache frequently accessed data using Azure Cache for Redis to reduce the load on the primary data store. This is especially effective when the resolution involves reducing request rates to a service with strict throughput limits. Even a short cache TTL of 30 to 60 seconds can dramatically reduce the number of requests that reach the backend during traffic spikes.

Workload Management (Modern Approach)

-- Create a workload group for ETL processes
CREATE WORKLOAD GROUP wgETL
WITH (
    MIN_PERCENTAGE_RESOURCE = 25,        -- Reserve 25% of resources
    CAP_PERCENTAGE_RESOURCE = 50,        -- Cap at 50% of resources
    REQUEST_MIN_RESOURCE_GRANT_PERCENT = 12.5,  -- Each request gets at least 12.5%
    REQUEST_MAX_RESOURCE_GRANT_PERCENT = 25,     -- Each request gets at most 25%
    IMPORTANCE = HIGH,
    QUERY_EXECUTION_TIMEOUT_SEC = 3600   -- 1 hour timeout
);

-- Create a workload group for ad-hoc queries
CREATE WORKLOAD GROUP wgAdHoc
WITH (
    MIN_PERCENTAGE_RESOURCE = 10,
    CAP_PERCENTAGE_RESOURCE = 25,
    REQUEST_MIN_RESOURCE_GRANT_PERCENT = 6.25,
    REQUEST_MAX_RESOURCE_GRANT_PERCENT = 6.25,
    IMPORTANCE = BELOW_NORMAL
);

-- Create classifiers to route queries to workload groups
CREATE WORKLOAD CLASSIFIER clsETL
WITH (
    WORKLOAD_GROUP = 'wgETL',
    MEMBERNAME = 'etl_user',
    IMPORTANCE = HIGH
);

CREATE WORKLOAD CLASSIFIER clsAdHoc
WITH (
    WORKLOAD_GROUP = 'wgAdHoc',
    MEMBERNAME = 'analyst_user',
    IMPORTANCE = BELOW_NORMAL
);

-- Create a classifier based on label
CREATE WORKLOAD CLASSIFIER clsReport
WITH (
    WORKLOAD_GROUP = 'wgETL',
    LABEL = 'ReportQuery',
    IMPORTANCE = HIGH
);

Monitoring Workload Groups

-- Check workload group utilization
SELECT 
    name,
    min_percentage_resource,
    cap_percentage_resource,
    effective_min_percentage_resource,
    effective_cap_percentage_resource,
    effective_request_min_resource_grant_percent,
    effective_request_max_resource_grant_percent,
    active_request_count,
    queued_request_count
FROM sys.dm_workload_management_workload_groups_stats;

-- Check classifier effectiveness
SELECT 
    name AS ClassifierName,
    membername,
    workload_group_name,
    importance
FROM sys.workload_management_workload_classifiers c
JOIN sys.workload_management_workload_classifier_details d 
    ON c.classifier_id = d.classifier_id;

-- Find which classifier a request was assigned to
SELECT 
    r.request_id,
    r.resource_class,
    r.group_name,
    r.importance,
    r.classifier_name,
    r.command
FROM sys.dm_pdw_exec_requests r
WHERE r.classifier_name IS NOT NULL
ORDER BY r.submit_time DESC;

Understanding Azure Service Limits and Quotas

Every Azure service operates within defined limits and quotas that govern the maximum throughput, connection count, request rate, and resource capacity available to your subscription. These limits exist to protect the multi-tenant platform from noisy-neighbor effects and to ensure fair resource allocation across all customers. When your workload approaches or exceeds these limits, the service enforces them through throttling (HTTP 429 responses), request rejection, or degraded performance.

Azure service limits fall into two categories: soft limits that can be increased through a support request, and hard limits that represent fundamental architectural constraints of the service. Before designing your architecture, review the published limits for every Azure service in your solution. Plan for the worst case: what happens when you hit the limit during a traffic spike? Your application should handle throttled responses gracefully rather than failing catastrophically.

Use Azure Monitor to track your current utilization as a percentage of your quota limits. Create dashboards that show utilization trends over time and set alerts at 70 percent and 90 percent of your limits. When you approach a soft limit, submit a quota increase request proactively rather than waiting for a production incident. Microsoft typically processes quota increase requests within a few business days, but during high-demand periods it may take longer.

For services that support multiple tiers or SKUs, evaluate whether upgrading to a higher tier provides the headroom you need. Compare the cost of the upgrade against the cost of engineering effort to work around the current limits. Sometimes, paying for a higher service tier is more cost-effective than building complex application-level sharding, caching, or load-balancing logic to stay within the lower tier’s constraints.

Disaster Recovery and Business Continuity

When resolving service issues, consider the broader disaster recovery and business continuity implications. If Resource Class and Workload Management Issues in Synapse is a critical dependency, your Recovery Time Objective (RTO) and Recovery Point Objective (RPO) determine how quickly you need to restore service and how much data loss is acceptable.

Implement a multi-region deployment strategy for business-critical services. Azure paired regions provide automatic data replication and prioritized recovery during regional outages. Configure your application to failover to the secondary region when the primary region is unavailable. Test your failover procedures regularly to ensure they work correctly and meet your RTO targets.

Maintain infrastructure-as-code templates for all your Azure resources so you can redeploy your entire environment in a new region if necessary. Store these templates in a geographically redundant source code repository. Document the manual steps required to complete a region failover, including DNS changes, connection string updates, and data synchronization verification.

Memory Pressure and Tempdb

-- Check tempdb usage
SELECT 
    SUM(total_allocation_user_K)/1024 AS TempDB_User_MB,
    SUM(total_allocation_internal_K)/1024 AS TempDB_Internal_MB,
    SUM(total_allocation_user_K + total_allocation_internal_K)/1024 AS TempDB_Total_MB
FROM sys.dm_pdw_nodes_exec_query_memory_grants;

-- Check for queries with high memory grants
SELECT TOP 10
    r.request_id,
    r.resource_class,
    r.total_elapsed_time,
    r.resource_allocation_percentage,
    r.command
FROM sys.dm_pdw_exec_requests r
WHERE r.status = 'Running'
ORDER BY r.resource_allocation_percentage DESC;

Best Practices

  • Use workload management over legacy resource classes for new implementations
  • Separate ETL from reporting — Give ETL larger resource grants and higher importance
  • Set query timeouts — Prevent runaway queries from blocking concurrency slots
  • Monitor queue depth — If queries are consistently queued, scale up the DW level or optimize resource allocation
  • Right-size resource grants — Start with smallrc and increase only when queries fail with memory errors

Capacity Planning and Forecasting

The most effective resolution is preventing the issue from recurring through proactive capacity planning. Establish a regular review cadence where you analyze growth trends in your service utilization metrics and project when you will approach limits.

Use Azure Monitor metrics to track the key capacity indicators for Resource Class and Workload Management Issues in Synapse over time. Create a capacity planning workbook that shows current utilization as a percentage of your provisioned limits, the growth rate over the past 30, 60, and 90 days, and projected dates when you will reach 80 percent and 100 percent of capacity. Share this workbook with your engineering leadership to support proactive scaling decisions.

Factor in planned events that will drive usage spikes. Product launches, marketing campaigns, seasonal traffic patterns, and batch processing schedules all create predictable demand increases that should be accounted for in your capacity plan. If your application serves a global audience, consider time-zone-based traffic distribution and scale accordingly.

Implement autoscaling where the service supports it. Azure autoscale rules can automatically adjust capacity based on real-time metrics. Configure scale-out rules that trigger before you reach limits (at 70 percent utilization) and scale-in rules that safely reduce capacity during low-traffic periods to optimize costs. Test your autoscale rules under load to verify that they respond quickly enough to protect against sudden traffic spikes.

Summary

Synapse resource class issues come from three sources: queries queuing because all concurrency slots are consumed (check with sys.dm_pdw_exec_requests), queries failing due to insufficient memory (upgrade resource class), and unbalanced workload allocation (use workload management groups). Migrate from legacy resource classes (sp_addrolemember) to workload management (CREATE WORKLOAD GROUP + CREATE WORKLOAD CLASSIFIER) for granular control over resource allocation and query prioritization.

For more details, refer to the official documentation: What is Azure Synapse Analytics?, Synapse Studio troubleshooting, Get started with Azure Synapse Analytics.

Leave a Reply