Azure SQL Database resource limits

Overview

Azure SQL Database manages the resources available to a database using two different mechanisms: Resources Governance and Enforcement of Limits. This topic explains these two main areas of resource management.

Resource governance

One of the design goals of the Basic, Standard, and Premium service tiers is for Azure SQL Database to behave as if the database is running on its own machine, isolated from other databases. Resource governance emulates this behavior. If the aggregated resource utilization reaches the maximum available CPU, Memory, Log I/O, and Data I/O resources assigned to the database, resource governance queues queries in execution and assign resources to the queued queries as they free up.

As on a dedicated machine, utilizing all available resources results in a longer execution of currently executing queries, which can result in command timeouts on the client. Applications with aggressive retry logic and applications that execute queries against the database with a high frequency can encounter errors messages when trying to execute new queries when the limit of concurrent requests has been reached.

Recommendations:

Monitor the resource utilization and the average response times of queries when nearing the maximum utilization of a database. When encountering higher query latencies you generally have three options:

  1. Reduce the number of incoming requests to the database to prevent timeout and the pile up of requests.
  2. Assign a higher performance level to the database.
  3. Optimize queries to reduce the resource utilization of each query. For more information, see the Query Tuning/Hinting section in the Azure SQL Database Performance Guidance article.

Enforcement of limits

Resources other than CPU, Memory, Log I/O, and Data I/O are enforced by denying new requests when limits are reached. When a database reaches the configured maximum size limit, inserts and updates that increase data size fail, while selects and deletes continue to work. Clients receive an error message depending on the limit that has been reached.

For example, the number of connections to a SQL database and the number of concurrent requests that can be processed are restricted. SQL Database allows the number of connections to the database to be greater than the number of concurrent requests to support connection pooling. While the number of connections that are available can easily be controlled by the application, the number of parallel requests is often times harder to estimate and to control. Especially during peak loads when the application either sends too many requests or the database reaches its resource limits and starts piling up worker threads due to longer running queries, errors can be encountered.

Service tiers and performance levels

There are service tiers and performance levels for both single database and elastic pools.

Single databases

For a single database, the limits of a database are defined by the database service tier and performance level. The following table describes the characteristics of Basic, Standard, and Premium databases at varying performance levels.

Basic service tier

Service tier Basic
Max DTUs 5
Max database size* 2 GB
Max in-memory OLTP storage N/A
Max concurrent workers 30
Max concurrent logins 30
Max concurrent sessions 300

Standard service tier

Service tier S0 S1 S2 S3
Max DTUs 10 20 50 100
Max database size* 250 GB 250 GB 250 GB 250 GB
Max in-memory OLTP storage N/A N/A N/A N/A
Max concurrent workers 60 90 120 200
Max concurrent logins 60 90 120 200
Max concurrent sessions 600 900 1200 2400

Premium service tier

Service tier P1 P2 P4 P6 P11 P15
Max DTUs 125 250 500 1000 1750 4000
Max database size* 500 GB 500 GB 500 GB 500 GB 1 TB 1 TB
Max in-memory OLTP storage 1 GB 2 GB 4 GB 8 GB 14 GB 32 GB
Max concurrent workers 200 400 800 1600 2400 6400
Max concurrent logins 200 400 800 1600 2400 6400
Max concurrent sessions 30000 30000 30000 30000 30000 30000

* Max database size refers to the maximum size of the data files and does not include the space used by log files.

Elastic pools

Elastic pools share resources across databases in the pool. The following table describes the characteristics of Basic, Standard, and Premium elastic pools.

Basic elastic pool limits

Pool size (eDTUs) 50 100 200 300 400 800 1200 1600
Max storage per pool* 5 GB 10 GB 20 GB 29 GB 39 GB 78 GB 117 GB 156 GB
Max In-Memory OLTP storage per pool* N/A N/A N/A N/A N/A N/A N/A N/A
Max number DBs per pool 100 200 500 500 500 500 500 500
Max concurrent workers per pool 100 200 400 600 800 1600 2400 3200
Max concurrent logins per pool 100 200 400 600 800 1600 2400 3200
Max concurrent sessions per pool 30000 30000 30000 30000 30000 30000 30000 30000
Min eDTUs per database {0, 5} {0, 5} {0, 5} {0, 5} {0, 5} {0, 5} {0, 5} {0, 5}
Max eDTUs per database {5} {5} {5} {5} {5} {5} {5} {5}

Standard elastic pool limits

Pool size (eDTUs) 50 100 200 300 400 800
Max storage per pool* 50 GB 100 GB 200 GB 300 GB 400 GB 800 GB
Max In-Memory OLTP storage per pool* N/A N/A N/A N/A N/A N/A
Max number DBs per pool 100 200 500 500 500 500
Max concurrent workers per pool 100 200 400 600 800 1600
Max concurrent logins per pool 100 200 400 600 800 1600
Max concurrent sessions per pool 30000 30000 30000 30000 30000 30000
Min eDTUs per database {0,10,20,
50}
{0,10,20,
50,100}
{0,10,20,
50,100}
{0,10,20,
50,100}
{0,10,20,
50,100}
{0,10,20,
50,100}
Max eDTUs per database {10,20,
50}
{10,20,
50,100}
{10,20,
50,100}
{10,20,
50,100}
{10,20,
50,100}
{10,20,
50,100}

Standard elastic pool limits (continued)

Pool size (eDTUs) 1200 1600 2000 2500 3000
Max storage per pool* 1.2 TB 1.6 TB 2 TB 2.4 TB 2.9 TB
Max In-Memory OLTP storage per pool* N/A N/A N/A N/A N/A
Max number DBs per pool 500 500 500 500 500
Max concurrent workers per pool 2400 3200 4000 5000 6000
Max concurrent logins per pool 2400 3200 4000 5000 6000
Max concurrent sessions per pool 30000 30000 30000 30000 30000
Min eDTUs per database {0,10,20,
50,100}
{0,10,20,
50,100}
{0,10,20,
50,100}
{0,10,20,
50,100}
{0,10,20,
50,100}
Max eDTUs per database {10,20,
50,100}
{10,20,
50,100}
{10,20,
50,100}
{10,20,
50,100}
{10,20,
50,100}

Premium elastic pool limits

Pool size (eDTUs) 125 250 500 1000 1500
Max storage per pool* 250 GB 500 GB 750 GB 750 GB 750 GB
Max In-Memory OLTP storage per pool* 1 GB 2 GB 4 GB 10 GB 12 GB
Max number DBs per pool 50 100 100 100 100
Max concurrent workers per pool 200 400 800 1600 2400
Max concurrent logins per pool 200 400 800 1600 2400
Max concurrent sessions per pool 30000 30000 30000 30000 30000
Min eDTUs per database {0,25,50,75,
125}
{0,25,50,75,
125,250}
{0,25,50,75,
125,250,500}
{0,25,50,75,
125,250,500,
1000}
{0,25,50,75,
125,250,500,
1000,1500}
Max eDTUs per database {25,50,75,
125}
{25,50,75,
125,250}
{25,50,75,
125,250,500}
{25,50,75,
125,250,500,
1000}
{25,50,75,
125,250,500,
1000,1500}

Premium elastic pool limits (continued)

Pool size (eDTUs) 2000 2500 3000 3500 4000
Max storage per pool* 750 GB 750 GB 750 GB 750 GB 750 GB
Max In-Memory OLTP storage per pool* 16 GB 20 GB 24 GB 28 GB 32 GB
Max number DBs per pool 100 100 100 100 100
Max concurrent workers per pool 3200 4000 4800 5600 6400
Max concurrent logins per pool 3200 4000 4800 5600 6400
Max concurrent sessions per pool 30000 30000 30000 30000 30000
Min eDTUs per database {0,25,50,75,
125,250,500,
1000,1750}
{0,25,50,75,
125,250,500,
1000,1750}
{0,25,50,75,
125,250,500,
1000,1750}
{0,25,50,75,
125,250,500,
1000,1750}
{0,25,50,75,
125,250,500,
1000,1750,4000}
Max eDTUs per database {25,50,75,
125,250,500,
1000,1750}
{25,50,75,
125,250,500,
1000,1750}
{25,50,75,
125,250,500,
1000,1750}
{25,50,75,
125,250,500,
1000,1750}
{25,50,75,
125,250,500,
1000,1750,4000}

* Pooled databases share pool storage, so database storage is limited to the smaller of the remaining pool storage or max storage per database. Max storage per pool refers to the maximum storage of the data files in the pool and does not include space used by log files.

For an expanded definition of each resource listed in the previous tables, see the descriptions in Service tier capabilities and limits. For an overview of service tiers, see Azure SQL Database Service Tiers and Performance Levels.

Other SQL Database limits

Area Limit Description
Databases using Automated export per subscription 10 Automated export allows you to create a custom schedule for backing up your SQL databases. The preview of this feature will end on March 1, 2017.
Database per server Up to 5000 Up to 5000 databases are allowed per server on V12 servers.
DTUs per server 45000 45000 DTUs are available per server on V12 servers for provisioning databases, elastic pools, and data warehouses.
Important

Azure SQL Database Automated Export is now in preview and will be retired on March 1, 2017. Starting December 1st, 2016, you will no longer be able to configure automated export on any SQL database. All your existing automated export jobs will continue to work until March 1st, 2017. After December 1st, 2016, you can use long-term backup retention or Azure Automation to archive SQL databases periodically using PowerShell periodically according to a schedule of your choice. For a sample script, you can download the sample script from Github.

Resources

Azure Subscription and Service Limits, Quotas, and Constraints

Azure SQL Database Service Tiers and Performance Levels

Error messages for SQL Database client programs