Azure SQL Database "DTU percentage" metric

Question

With the new Azure SQL Database tier structure, it seems important to monitor your database "DTU" usage to know whether to upgrade or downgrade to another tier.

When reading Azure SQL Database Service Tiers and Performance Levels, it only talks about monitoring with CPU, Data and Log percentage usage.

But, when I add new metrics, I also have an DTU percentage option:

I can't find any about this online. Is this essentially a summary of the other DTU-related metrics?

Solution 1

A DTU is a unit of measure for the performance of a service tier and is a summary of several database characteristics. Each service tier has a certain number of DTUs assigned to it as an easy way to compare the performance level of one tier versus another.

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.

The DTU Quota applies to the server, not the individual databases and each server has a maximum of 1600 DTUs. The DTU% is the percentage of units your particular database is using and it seems that this number can go over 100% of the DTU rating of the service tier (I assume to the limit of the server). This percentage number is designed to help you choose the appropriate service tier.

From down toward the bottom of this announcement:

For example, if your DTU consumption shows a value of 80%, it indicates it is consuming DTU at the rate of 80% of the limit an S2 database would have. If you see values greater than 100% in this view it means that you need a performance tier larger than S2.

As an example, let’s say you see a percentage value of 300%. This tells you that you are using three times more resources than would be available in an S2. To determine a reasonable starting size, compare the DTUs available in an S2 (50 DTUs) with the next higher sizes (P1 = 100 DTUs, or 200% of S2, P2 = 200 DTUs or 400% of S2). Because you are at 300% of S2 you would want to start with a P2 and re-test.

Solution 2

Still not cool enough to comment, but regarding @vladislav's comment the original article was fairly old. Here is an update document regarding DTU's, which would help answer the OP's question.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-what-is-a-dtu

Solution 3

From this document, this DTU percent is determined by this query:

SELECT end_time,   
  (SELECT Max(v)    
   FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), 
(avg_log_write_percent)) AS    
   value(v)) AS [avg_DTU_percent]   
FROM sys.dm_db_resource_stats;  

looks like the max of avg_cpu_percent, avg_data_io_percent and avg_log_write_percent

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-resource-stats-azure-sql-database

Solution 4

DTU is nothing but a blend of CPU, memory and IO. Why do we need a blend when these 3 are pretty clear? Because we want a unit for power. But it is still confusing in many ways. eg: If I simply increase memory will it increase power(DTU)? If yes, how can DTU be a blend? It is a yes. In this memory-increase case, as per the query in the answer given by jyong, DTU will be equivalent to memory(since we increased it). MS has even a pricing model based on this DTU and it raised many questions.

Because of these confusions and questions, MS wanted to bring in another option. We already had some specs in on-premise, why can't we use them? As a result, 'vCore pricing model' was born. In this model we have visibility to RAM and CPU. But not in DTU model.

The counter argument from DTU would be that DTU measures are calibrated using a benchmark that simulates real-world database workload. And that we are not in on-premise anymore ;). Yes it is designed with cloud computing in mind(but is also used in OLTP workloads).

But that is not all. Now that we are entering the pricing model the equation changes. The question now is about money and the bundle(what all features are included). Here DTU has some advantages(the way I see it) but enterprises with many existing licenses would disagree.

• DTU has one pricing(Compute + Storage + Backup). Simpler and can start with lower pricing.
• vCore has different pricing (Compute, Storage). Software assurance is available here. Enterprises will have on-premise licenses, this can be easily ported here(so they get big machines for less price than DTU model). Plus they commit for multiple years and get additional discounts.

We can switch between both when needed so if not sure start with DTU(Basic/Standard/Premium).

How can we know which pricing tier to use? Go to configure menu as given below: (on the right/left you can switch between both)

Even though Vcore is bigger 'machine' and for bigger things, the cost can sometimes be cheaper for enterprise organizations. Here is a proof. DTU costs $147 . But Vcore costs $111. That is because you can commit for 3 years(but still pay monthly) and also because of the license re-use option(enterprises will have on-premise licenses).

It is a bit too much than answering direct question but I am gonna go ahead and make this complete by answering 'how to choose between different options in DTU let alone choosing between DTU and vCore'. This is answered in this beautiful blog and this flowchart explains it all

View more solutions

Comments

[Sumit Gupta]

I am searching and still didn't find it, what this DTU Percentage show if we are already on some new Tier? like if I am on S0, does the DTU Percentage now shows of S0 graph or still shows "as compare to s2" ? It is really simple thing that is unclear.

[Andrew]

@SumitGupta the graph gets reset/emptied when you change tier, so you have no old data to look at, and the new tier DTU scale is in effect right away.

[Vladislav]

I'm currently using Azure SQL and have few databases. The quota limit per server that you mention here is not correct, or at least it is outdated. DTU quotas are determined by the pricing tier which is specific for each database, not server. Maybe the reference link that you quote has been changed after your post. ...or do I miss something? Thanks.

[Ayushmati]

So informative! The screenshots from azure portal are especially helpful.

[Ayushmati]

This link is the one azure portal provides as an answer to the question "What is a DTU". Thanks for this.

[aruno]

This same formula is shown on the purchasing-models page (in english!) docs.microsoft.com/en-us/azure/azure-sql/database/…