Azure SQL Database: Service Tiers & Performance Q&A

A few days ago, I published a post with some anticipated questions & answers to provide details on the new service tiers for Microsoft Azure SQL Database, announced on April 24. In this follow-up post, I want to provide more information about how SQL Database performance is factored into the service tiers.

Like the previous post, this document was originally written to help people on the Microsoft team address common questions about the new service tiers, and the information is certainly relevant to you, as well.

Shawn Bice
Director of Program Management, Data Platform Group

How is SQL Database performance improving with the new service tiers?

Our customers have provided consistent feedback that they highly value predictable performance. To address this feedback, we previously introduced a Premium service tier to support database workloads with higher-end throughput needs. We’re continuing our commitment to predictable performance by introducing new service tiers at lower price points (Basic & Standard), which are primarily differentiated on performance. As you move up the performance levels, the available throughput increases. This service design offers customers the opportunity to dial up the right set of resources to get the throughput their database requires.

What changes are being made to Premium?

Starting April 24, Azure SQL Database Premium preview introduces a new 500 GB max size, another performance level (P3), new business continuity features (active geo-replication and self-service restore), and a streamlined provisioning and billing experience.

What new features are available in Premium?

Active Geo-Replication: Gain control over your disaster recovery process by creating up to four active, readable secondaries in any Azure region and choosing when to failover. For more information on using Active Geo-Replication, see Disaster Recovery documentation.

Self-service Restore: SQL Database Premium allows you to restore your database to any point in time within the last 35 days, in the case of a human or programmatic data deletion scenario. Replace import/export workarounds with self-service control over database restore. For more on using Self-service Restore, see Restore Service documentation.

Larger database size: support for up to 500 GB databases is baked into the daily rate (no separate charge for DB size).

Additional Premium performance level: Meet high-end throughput needs with a new P3 performance level which delivers the greatest performance for your most demanding database workloads. Learn more about SQL Database Premium and pricing by visiting the SQL Database pricing page.

What are performance levels?

Azure SQL Database service tiers introduce the concept of performance levels. There are six performance levels across the Basic, Standard, and Premium service tiers. The performance levels are Basic, S1, S2, P1, P2, and P3. Each performance level delivers a set of resources required to run light-weight to heavy-weight database workloads.

How does a customer think about the performance power available across the different performance levels?

As part of providing a more predictable performance experience for customers, SQL Database is introducing the Database Throughput Unit (DTU). A DTU represents the power of the database engine as a blended measure of CPU, memory, and read and write rates. This measure helps a customer assess the relative power of the six SQL Database performance levels (Basic, S1, S2, P1, P2, and P3). 

Performance levels offer the following DTUs:

How can a customer choose a performance level without hardware specs?

We understand the on-premises and VM world have made machine specs the go-to option for assessing potential power a system can provide to database workloads. However, this approach doesn’t translate in the platform-as-a-service world where abstracting the underlying hardware and OS is inherent to the value proposition and overall customer benefit.

Customers consistently tell us they assess performance needs for building cloud-designed applications by choosing a performance level, building the app, and then testing and tuning the app, as needed. The complicated task of assessing hardware specs is more critical in the on-premises world where the ability to scale up requires more careful consideration and calculation. With database-as-a-service, picking an option, then dialing up (or down) performance power is a simple task via an API or the Azure portal.

Review the performance guide on MSDN for more information.

How can a customer view the utilization of the resources in a performance level?

Customers can monitor the percentage of available CPU, memory, and read and write IO that is being consumed over time. Initially, customers will not see memory consumption, but this will be added to the views during the course of preview.

What do we mean by a transaction rate per hour, per minute, and per second?

Each of the performance levels is designed to deliver increasingly higher throughput. By summarizing the throughput of each service tier as supporting transaction rates per-hour, per-minute, and per-second, it makes it easier for customers to quickly relate the capabilities of the service tier to the requirements of an application. Basic, for example, is designed for applications that measure activity in terms of transactions per hour. An example might be a single point-of-sale (POS) terminal in a bakery shop selling hundreds of items in an hour as the required throughput. Standard is designed for applications with throughput measured in terms of tens or hundreds of transactions per minute. Premium is designed for the most intense, mission-critical throughput, where support for many hundreds of concurrent transactions per second is required.

What if a customer needs to understand DTU power in more precise numbers, a language they understand?

For customers looking for a familiar reference point to assist in selecting an appropriate performance level, Microsoft is publishing OLTP benchmark numbers for each of the 6 performance levels (Basic, S1, S2, P1, P2, and P3). These published transaction rates are the output of an internal Microsoft cloud benchmark which mimics the database workload of a typical OLTP cloud application. As with all benchmarks, the published transaction rates are only a guide. Real-world databases are of different sizes and complexity, encounter different mixes of workloads, and will respond in different ways.  Nonetheless, the published transaction rates will help customers understand the relative throughput of each performance level. The published Microsoft benchmark transaction rates are as follows, and the methodology paper is here.

The car industry provides a great analogy when thinking about DTUs. While horsepower is used to measure the power of an engine, a sports car and a truck utilize this horsepower in very different ways to achieve different results. Likewise, databases will use DTU power to achieve different results, depending on the type of workload. The Microsoft benchmark numbers are based on a single defined OLTP workload (the sports car, for example).  Customers will need to assess this for their unique workload needs.

Defining database power via a published benchmark is a cloud analog of TPC-C. TPC-C is the traditional industry-standard approach for defining the highest power potential of a database workload. Customers familiar with traditional databases and database systems will immediately understand the value and caveats associated with benchmark numbers. We have found newer startups and developers to be less familiar with the benchmarking industry.  Instead, this group is more motivated to just build, test, and tune.

By offering customers both the benchmark-defined transaction rates and the ability to quickly build, try, and tune (scale up or down), we believe most customer performance assessment needs will be met.

Are the published transaction rates a throughput guarantee?

The Microsoft benchmark and associated transaction rates do not represent a transaction guarantee to customers. Notwithstanding the differences in customer workloads, customers cannot bank transactions for large bursts or roll transactions over seconds, minutes, hours, days, etc. The best way for customers to assess their actual performance needs is to view their actual resource usage in the Azure portal. Detailed views show usage over time as a percentage of the available CPU, memory, reads, and writes within their defined performance level.

Why doesn’t Microsoft just publish a benchmark on TPC, the industry-standard in database benchmarking?

Currently, TPC does not permit cloud providers to publish TPC benchmarks for database workloads. There is no other cloud vendor industry standard established at this time.

Will Microsoft publish the benchmark code for customers to run in their own environment?

Currently, there are no plans to publish the benchmark to customers. However, Microsoft will publish the methodology details (here) of how the defined OLTP workload was run to achieve the published benchmark numbers.

SQL Server Team

See more articles from this author