Deeper Look At the Database Transaction Unit Purchasing Model

 

Database Transaction Unit Purchasing Model

What is DTU?

• Database transaction units are an Azure proprietary measure of available resources.
• This purchasing model may be used by single databases and elastic pools.
• A DTU is an abstraction of representative OLTP database performance encompassing CPU, memory, disk reads, and disk writes in a single unit of measure.
• Microsoft developed their own proprietary benchmark testing of what they have deemed typical OLTP database activity to define the typical performance levels of those 4 performance areas (CPU, memory, disk reads, and disk writes).
• DTU is often illustrated using a graph that contains a dimension for each of these performance areas.
• The typical values determined by Microsoft effectively create a performance envelope on the graph, implying that a DTU is capable of providing that much oomph for a particular performance area, but no more. 
• DTU is intended to be an abstraction of overall performance, a simplification.
• What we do need to understand regarding DTUs is that the DTU values are a proportional measure of available resource power.
• Example, if we compare to a database that was allocated 10 DTUs with a database that had been allocated 3000 DTUs, a 3000 DTU database is 300 times as powerful as the 10 DTU database.
• There's no way to allocate only additional memory or only improve read IOPS to a database using the DTU purchasing model.

What happens if you make your database work really hard? 

• Azure uses resource governance to constrain performance within the DTU envelope. 
• Database workloads will be throttled in order to keep them within the performance envelope for the selected DTU class. 
• It's very easy to change the DTUs allocated to a database and can be done with very minimal service downtime, assuming you even notice.
• So if you've decided that the lack of hardware resources is the cause of your database's problems, you can simply change to a larger DTU class to increase your performance limits.

What's a DTU class?

DTU classes are how Azure identifies the available selections for your Azure SQL resources when you are configuring them to use the DTU purchasing model. 

Selection of DTU values are limited to predefined classes within a service tier. Each number on the slider line represents a valid class selection, and each class can also be identified by a class code as well. You cannot select a DTU value outside of these classes. So while you might have decided that 300 DTU is just perfect for your database, here the only selectable classes are either 200 or 400 DTUs.

Basic service tier:

• It's only available with the DTU model.
• Databases using the Basic service tier are fixed at 5 DTUs, have a maximum database size of 2 gigabytes, and a maximum point-in-time retention of 7 days.

The Standard service tier:

• It is the default service tier for the DTU purchasing model
• Its supporting options ranging from 10 to 3000 DTUs, and supporting databases from 100 megabytes to 1 terabyte in size, again, depending on the selected DTU class.

The Premium service tier:

• It supports from 125 to 4,000 DTUs, databases up to 4 terabytes in size, as well as additional features to enhance the availability of your databases.

Classes of Standard

• The lowest 3 classes of Standard (S0, S1, and S2) share some limitations with the Basic service tier.
• Databases using these classes use less than a single vCore on the supporting hardware. 
• That means behind the scenes, they have to share a virtual CPU core with other databases.
• Data in these classes is stored using Standard page blobs in Azure storage, which means they're stored on old-school, spinning-platter hard disk drives.
• The higher standard DTU classes use Premium storage,which uses solid state drives, giving better I/O performance. 
• None of these service tier classes support creating columnstore indexes.

What's point-in-time retention?

• It's the amount of time in which you can restore an Azure SQL database to any specific point in time.
• PITR cannot be increased while using the Basic service tier.
• All long-term retention options are supported out to 10 years but only provide full database restore capability.
• DTU service tier are not recommended for production. These are relatively inexpensive and useful for development and testing environments, databases using the workloads. 

Reference: 

Purchasing models - Azure SQL Database | Microsoft Learn

DTU-based purchasing model - Azure SQL Database | Microsoft Learn

Elastic Pools and DTUs? How's that work?