Memory and concurrency limits for Azure SQL Data Warehouse

View the memory and concurrency limits allocated to the various performance levels and resource classes in Azure SQL Data Warehouse. For more information, and to apply these capabilities to your workload management plan, see Resource classes for workload management.

Currently there are two generations available with SQL Data Warehouse – Gen1 and Gen2. We recommend you leverage Gen2 of SQL Data Warehouse to get the best performance for your data warehouse workload. Gen2 introduces a new NVMe Solid State Disk cache that keeps the most frequently accessed data close to the CPUs. This removes the remote I/O for your most intensive and demanding workloads. In addition to performance, Gen2 offers the greatest level of scale by enabling you to scale up to 30,000 Data Warehouse Units and providing unlimited columnar storage. We will still support the previous generation (Gen1) of SQL Data Warehouse and retain the same features; however, we encourage you to upgrade to Gen2 at your earliest convenience.

Data warehouse capacity settings

The following tables show the maximum capacity for the data warehouse at different performance levels. To change the performance level, see Scale compute - portal.

Gen2

Gen2 provides 2.5x more memory per query than the Gen1. This extra memory helps the Gen2 deliver its fast performance. The performance levels for the Gen2 range from DW100c to DW30000c.

Performance level Compute nodes Distributions per Compute node Memory per data warehouse (GB)
DW100c 1 60 60
DW200c 1 60 120
DW300c 1 60 180
DW400c 1 60 240
DW500c 1 60 300
DW1000c 2 30 600
DW1500c 3 20 900
DW2000c 4 15 1200
DW2500c 5 12 1500
DW3000c 6 10 1800
DW5000c 10 6 3000
DW6000c 12 5 3600
DW7500c 15 4 4500
DW10000c 20 3 6000
DW15000c 30 2 9000
DW30000c 60 1 18000

The maximum Gen2 DWU is DW30000c, which has 60 Compute nodes and one distribution per Compute node. For example, a 600 TB data warehouse at DW30000c processes approximately 10 TB per Compute node.

Gen1

The service levels for Gen1 range from DW100 to DW6000.

Performance level Compute nodes Distributions per Compute node Memory per data warehouse (GB)
DW100 1 60 24
DW200 2 30 48
DW300 3 20 72
DW400 4 15 96
DW500 5 12 120
DW600 6 10 144
DW1000 10 6 240
DW1200 12 5 288
DW1500 15 4 360
DW2000 20 3 480
DW3000 30 2 720
DW6000 60 1 1440

Concurrency maximums

To ensure each query has enough resources to execute efficiently, SQL Data Warehouse tracks resource utilization by assigning concurrency slots to each query. The system puts queries into a queue where they wait until enough concurrency slots are available. Concurrency slots also determine CPU prioritization. For more information, see Analyze your workload

Gen2

Static resource classes

The following table shows the maximum concurrent queries and concurrency slots for each static resource class.

Service Level Maximum concurrent queries Concurrency slots available staticrc10 staticrc20 staticrc30 staticrc40 staticrc50 staticrc60 staticrc70 staticrc80
DW100c 4 4 1 2 4 4 4 4 4 4
DW200c 8 8 1 2 4 8 8 8 8 8
DW300c 12 12 1 2 4 8 8 8 8 8
DW400c 16 16 1 2 4 8 16 16 16 16
DW500c 20 20 1 2 4 8 16 16 16 16
DW1000c 32 40 1 2 4 8 16 32 32 32
DW1500c 32 60 1 2 4 8 16 32 32 32
DW2000c 48 80 1 2 4 8 16 32 64 64
DW2500c 48 100 1 2 4 8 16 32 64 64
DW3000c 64 120 1 2 4 8 16 32 64 64
DW5000c 64 200 1 2 4 8 16 32 64 128
DW6000c 128 240 1 2 4 8 16 32 64 128
DW7500c 128 300 1 2 4 8 16 32 64 128
DW10000c 128 400 1 2 4 8 16 32 64 128
DW15000c 128 600 1 2 4 8 16 32 64 128
DW30000c 128 1200 1 2 4 8 16 32 64 128

Dynamic resource classes

Note

The smallrc resource class on Gen2 dynamically adds memory as the service level increases and only supports a max 32 concurrent queries at DW1000c and 4 and DW100c. Once the instance is scaled beyond DW1500c, the concurrency slots and memory used by smallrc increases as the service level increases.

The following table shows the maximum concurrent queries and concurrency slots for each dynamic resource class. Unlike Gen1, dynamic resource classes on Gen2 are truly dynamic. Gen2 uses a 3-10-22-70 memory percentage allocation for small-medium-large-xlarge resource classes across all service levels.

Service Level Maximum concurrent queries Concurrency slots available Slots used by smallrc Slots used by mediumrc Slots used by largerc Slots used by xlargerc
DW100c 4 4 1 1 1 2
DW200c 8 8 1 1 1 5
DW300c 12 12 1 1 2 8
DW400c 16 16 1 1 3 11
DW500c 20 20 1 2 4 14
DW1000c 32 40 1 4 8 28
DW1500c 32 60 1 6 13 42
DW2000c 32 80 2 8 17 56
DW2500c 32 100 3 10 22 70
DW3000c 32 120 3 12 26 84
DW5000c 32 200 6 20 44 140
DW6000c 32 240 7 24 52 168
DW7500c 32 300 9 30 66 210
DW10000c 32 400 12 40 88 280
DW15000c 32 600 18 60 132 420
DW30000c 32 1200 36 120 264 840

Gen1

Static resource classes

The following table shows the maximum concurrent queries and concurrency slots for each static resource class on Gen1.

Service level Maximum concurrent queries Maximum concurrency slots staticrc10 staticrc20 staticrc30 staticrc40 staticrc50 staticrc60 staticrc70 staticrc80
DW100 4 4 1 2 4 4 4 4 4 4
DW200 8 8 1 2 4 8 8 8 8 8
DW300 12 12 1 2 4 8 8 8 8 8
DW400 16 16 1 2 4 8 16 16 16 16
DW500 20 20 1 2 4 8 16 16 16 16
DW600 24 24 1 2 4 8 16 16 16 16
DW1000 32 40 1 2 4 8 16 32 32 32
DW1200 32 48 1 2 4 8 16 32 32 32
DW1500 32 60 1 2 4 8 16 32 32 32
DW2000 48 80 1 2 4 8 16 32 64 64
DW3000 64 120 1 2 4 8 16 32 64 64
DW6000 128 240 1 2 4 8 16 32 64 128

Dynamic resource classes

Note

The smallrc resource class on Gen1 allocates a fixed amount of memory per query, similar in fashion to the static resource class staticrc10. Because smallrc is static, it has the ability to scale to 128 concurrent queries.

The following table shows the maximum concurrent queries and concurrency slots for each dynamic resource class on Gen1.

Service level Maximum concurrent queries Concurrency slots available smallrc mediumrc largerc xlargerc
DW100 4 4 1 1 2 4
DW200 8 8 1 2 4 8
DW300 12 12 1 2 4 8
DW400 16 16 1 4 8 16
DW500 20 20 1 4 8 16
DW600 24 24 1 4 8 16
DW1000 32 40 1 8 16 32
DW1200 32 48 1 8 16 32
DW1500 32 60 1 8 16 32
DW2000 48 80 1 16 32 64
DW3000 64 120 1 16 32 64
DW6000 128 240 1 32 64 128

When one of these thresholds is met, new queries are queued and executed on a first-in, first-out basis. As a queries finishes and the number of queries and slots fall below the limits, SQL Data Warehouse releases queued queries.

Next steps

To learn more about how to leverage resource classes to optimize your workload further please review the following articles: