Understanding Service Tiers and Versions

Azure SQL Database Benchmark test result references

Azure SQL Database Benchmark is a database benchmark designed by Microsoft to facilitate quantitative improvements for translating cloud computing resource allocation into higher database performance. This benchmark includes virtually all basic operations that occur within the online transaction processing (OLTP) workload. While the design of the benchmark is based on cloud computing, the database schema and data population and transactions are representative of a wide range of basic elements commonly used in OLTP workloads.

Features of Azure SQL Database Benchmark

The basic features of Azure SQL Database Benchmark include:

  • The database used for Azure SQL Database Benchmark uses six tables. Two of these tables are fixed-size tables, meaning that the number of rows does not change. Three of these tables are scaling tables, such that the performance achieved by the database is proportional to the number of lines in the tables. The final table is a growing table, the size of which is subject to data insertions and deletions performed while the benchmark is running.

  • The schema of the tables is composed of a mixture of data types, including integer, numeral, character, and date/time. It also includes primary and secondary keys, but does not include any foreign keys. For example, there are no referential integrity constraints among the tables.

  • The maximum performance is proportional to the size of the database and the concurrent connections. For example, a database with 100 concurrent connections can achieve a maximum transaction rate of 100 transactions per second (TPS), because the benchmark application has pacing control to ensure that every connection on average completes no more than a maximum of one transaction per second. More concurrent connections and a larger database are required to facilitate a higher TPS rate.

  • Azure SQL Database Benchmark includes nine different types of transaction. Each transaction type is focused on a specific set of system characteristics in the database engine and system hardware, and the different transaction types are focused on different areas. The transactions use four basic SQL data manipulation language (DML) operations (select, update, insert, and delete) to test the database engine and basic hardware resources such as CPU, memory, I/O, and networks.

For a more detailed explanation of Azure SQL Database Benchmark, refer to Overview of the Azure SQL Database Benchmark.

Benchmark test results from Azure SQL Database Benchmark

The table below shows the level of stable performance that can be maintained by each version that uses the Azure SQL Database Benchmark, as well as the corresponding number of concurrent connections. Each test was run for an hour or more under stable conditions.

Version

Transaction rate (TPS)

Test database size

Concurrent connections

MS 1

10

1.7GB

12

MS 2

20

3.0GB

22

MS 3

60

8.9GB

65

MS 4

200

28.6GB

210

MS 5

300

43.6GB

320

MS 6

450

73.6GB

470

Note

It is important to be aware that, like all benchmarks, Azure SQL Database Benchmark can provide only representative and indicative results. The transaction rates achieved by using the benchmark application will differ from those achieved by using other applications. The benchmark includes a collection of different transaction type. These transaction types are run to target a schema that includes a range of table and data types. While the benchmark performs common basic operations that are shared by all OLTP workloads, it does not represent any specific type of database or application. The goal of this benchmark is to provide a reasonable reference for a database’s expected relative performance when increasing or decreasing performance levels. In reality, databases vary in terms of size and complexity, encounter different workload mixes, and respond in different ways. For example, an I/O-intensive application might reach the I/O threshold very quickly, while a memory-intensive application might rapidly reach the memory limit. There is no guarantee that any specific database will perform as indicated by the Azure SQL Database Benchmark under increased load conditions.**