SALES: 1-800-867-1380

Azure SQL Database Service Tiers and Performance Levels

Updated: September 10, 2014

This topic describes the performance model for Azure SQL Database, and general guidance on how you can select a service tier and performance level.

Service Tiers & Performance Levels

Microsoft Azure SQL Database service tiers have the following performance characteristics:

  • Basic: Best suited for a small size database, supporting typically one single active operation at a given time. Examples include databases used for development or testing, or small scale infrequently used applications.

  • Standard: The go-to option for most cloud applications, supporting multiple concurrent queries. Examples include workgroup or web applications.

  • Premium: Designed for high transactional volume, supporting a large number of concurrent users and requiring the highest level of business continuity capabilities. Examples are databases supporting mission critical applications.



    noteNote
    Business and Web service tiers will be retired September 2015. For more information, see Web and Business Edition Sunset FAQ

Overview of the Performance Model

It is important to be able to quantify how the increasing power of each performance level translates into increased database performance. To do this, Microsoft developed the Azure SQL Database Benchmark (ASDB). The benchmark allows us to assess the impact on database performance of the increasing power offered by each performance level by measuring actual database operations, while scaling database size, number of users, and transaction rates in proportion to the hardware resources. The benchmark exercises a mix of basic operations found in all OLTP workloads.

Key terms used to describe the performance model:

  • Database Throughput Unit (DTU): DTUs provide a way to describe the relative capacity of a performance level of Basic, Standard, and Premium databases. DTUs are based on a blended measure of CPU, memory, reads, and writes. As DTUs increase, the power offered by the performance level increases. For example, a performance level with 5 DTUs has five times more power than a performance level with 1 DTU. A maximum DTU quota applies to each server. For more information on DTU quotas, see Azure SQL Database General Guidelines and Limitations.

  • Azure SQL Database Benchmark (ASDB): ASDB measures the actual throughput of a performance level by using a mix of database operations which occur most frequently in online transaction processing (OLTP) workloads. For an overview of the Benchmark, see the Benchmark section.

  • Transaction Rate: Transaction rate is the metric produced by the ASDB benchmark. It is reported in transactions per unit-of-time, counting all transaction types. In the Azure SQL Database Benchmark, throughput is reported in transaction per unit-of-time, with the time scale being shorter for higher classes of service, as shown below.

  • Predictability: Consistency of response time is an indication of performance predictability. A database which achieves a more stringent response time constraint delivers more predictable performance. In the Azure SQL Database Benchmark, the response time constraint varies with class of service, with higher classes of service having to satisfy a more stringent response time requirement.

 

Service Tier/Performance Level DTU MAX DB Size Max Worker Threads Max Sessions Benchmark Transaction Rate Predictability

Basic

5

2 GB

30

300

16,600 transactions per hour

Good

Standard/S0

10

250 GB

60

600

521 transactions per minute

Better

Standard/S1

20

250 GB

90

900

934 transactions per minute

Better

Standard/S2

50

250 GB

120

1,200

2,570 transactions per minute

Better

Premium/P1

100

500 GB

200

2,400

105 transactions per second

Best

Premium/P2

200

500 GB

400

4,800

228 transactions per second

Best

Premium/P3

800

500 GB

1,600

19,200

735 transactions per second

Best

How to Select a Service Tier and Performance Level

It is quick and easy to change between service tiers or performance levels. Changing service tiers and performance levels is an online operation – you can use the database throughout the operation. Take advantage of this flexibility to make cost effective decisions for your Azure SQL Database. For a new database that is in development, start with the Basic service tier. By monitoring your application performance, you can then work towards the optimum service tier and performance level.

In addition, if you have an application that has a fluctuating workload that is predictable, you can move between performance levels that matches the peaks and low points. A good example of such an application would be one that has workload peaks typically at the end of the month.

For more information on changing the service tiers or performance levels, see Changing Database Service Tiers and Performance Levels.

How to monitor performance and adjust the performance level accordingly

Monitoring the performance of a SQL Database starts with monitoring the resource utilization relative to the performance level you chose for your database. This relevant data is exposed in the following ways:

  1. The Microsoft Azure Management Portal.

  2. Dynamic Management Views in the logical master database of the server in which you created the database.

In the Azure Management Portal, you can monitor your database’s utilization by selecting your database and navigating to the monitoring tab. Add the metrics to the page by clicking Add Metrics at the bottom of the page. The performance metrics to monitor are the following:

  • CPU Percentage

  • Physical Data Reads percentage

  • Log Writes Percentage

Once you’ve added these metrics, you can select them on the monitoring page to have them plotted over time. All three metrics show the average utilization percentage relative to the DTU of your database.

You can configure alerts on the performance metrics. Select the metric on the monitoring page, and then at the bottom of the page click Add Rule. Follow the wizard to configure your alert. You have the option to alert if the metrics exceeds a certain threshold or if the metric falls below a certain threshold.

For example, if you expect the workload on your database to grow, you can chose to configure an email alert whenever your database reaches 80% on any of the performance metrics. You can use this as an early warning to figure out when you might have to switch to the next higher performance level.

Performance Metircs

The performance metrics can also help you determine if you are able to downgrade to a lower performance level. Assume you are using a Standard S2 database and all performance metrics show that the database on average does not use more than 10% at any given time. It is likely that the database will work well in Standard S1. However, be aware of workloads that spike or fluctuate before making the decision to move to a lower performance level. The same three metrics that are exposed in the portal are also exposed in the logical master database of your server through the catalog view sys.resource_stats and through the dynamic management view (DMV) sys.dm_db_resource_stats. Use sys.resource_stats if you need to monitor less granular data across a longer period of time. Use sys.dm_db_resource_stats if you need to monitor more granular data within a smaller timeframe. For more information, see Azure SQL Database Performance Guidance.

Benchmark

ASDB measures the performance of a mix of basic database operations which occur most frequently in online transaction processing (OLTP) workloads. Although the benchmark is designed with cloud computing in mind, the database schema, data population, and transactions have been designed to be broadly representative of the basic elements most commonly used in OLTP workloads.

noteNote
It is important to understand that ASDB, like all benchmarks, is representative and indicative only. The transaction rates achieved with the benchmark application will not be the same as those that might be achieved with other applications. The benchmark comprises a collection of different transaction types run against a schema containing a range of tables and data types. While the benchmark exercises the same basic operations that are common to all OLTP workloads, it does not represent any specific class of database or application. The goal of the benchmark is to provide a reasonable guide to the relative performance of a database that might be expected when scaling up or down between performance levels. In reality, databases are of different sizes and complexity, encounter different mixes of workloads, and will respond in different ways. For example, an IO intensive application may hit IO thresholds sooner, or a memory intensive application may hit memory limits sooner. There is no guarantee that any particular database will scale in the same way as the benchmark under increasing load.

Characteristics of the Azure SQL Database Benchmark

The benchmark runs against a database comprised of six tables with a range of cardinality (cardinality expressed as the number of rows in a table). The database has two fixed size tables with a constant number of rows, three scaling tables, where the number of rows is proportional to the database performance, and one table that is similar to the scaling tables, but varies in size during a benchmark run as rows are inserted and deleted.

The schema includes a mix of data types, including integer, numeric, character, and date/time. The schema includes primary and secondary keys, but not any foreign keys. For example, there are no referential integrity constraints between tables.

The database is sized so that the cardinality of the scaling and growing tables is proportional to both the number of users and the maximum performance. For example, a 100-user database can achieve a maximum rate of 100 TPS. To drive a higher TPS rate requires more users and a larger database. The workload consists of nine transaction types. Each transaction is designed to highlight a particular set of system characteristics in the database engine and system hardware with high contrast from the other transactions. The transactions utilize the four basic SQL DML operations of SELECT, UPDATE, INSERT, and DELETE to exercise both the database engine and the underlying hardware resources for CPU, memory, IO, and network.

For a description of the benchmark and resulting measurements, see Azure SQL Database Benchmark Overview.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft