[azure] Azure SQL Database "DTU percentage" metric

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:

Add Database Metrics

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

The answer is


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) VCore

DTU

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).

Cost DTU

enter image description here

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

enter image description here


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


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


To check the accurate usage for your services be it is free (as per always free or 12 months free) or Pay-As-You-Go, it is important to monitor the usage so that you know upfront on the cost incurred or when to upgrade your service tier.

To check your free service usage and its limits, Go to search in Portal, search with "Subscription" and click on it. you will see the details of each service that you have used.

In case of free azure from Microsoft, you get to see the cost incurred for each one.

Visit Check usage of free services included with your Azure free account enter image description here

Hope this helps someone!


Examples related to azure

How is VIP swapping + CNAMEs better than IP swapping + A records? 'Connect-MsolService' is not recognized as the name of a cmdlet Install-Module : The term 'Install-Module' is not recognized as the name of a cmdlet Could not load file or assembly "System.Net.Http, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" How to view the roles and permissions granted to any database user in Azure SQL server instance? How to get the azure account tenant Id? Azure SQL Database "DTU percentage" metric Could not load file or assembly System.Web.Http.WebHost after published to Azure web site How to re-create database for Entity Framework? How do I create a new user in a SQL Azure database?

Examples related to azure-sql-database

How to view the roles and permissions granted to any database user in Azure SQL server instance? Azure SQL Database "DTU percentage" metric Reset identity seed after deleting records in SQL Server How to re-create database for Entity Framework? The SELECT permission was denied on the object 'Users', database 'XXX', schema 'dbo' How do I create a new user in a SQL Azure database? How to emulate a BEFORE INSERT trigger in T-SQL / SQL Server for super/subtype (Inheritance) entities? Optimal way to concatenate/aggregate strings How do I copy SQL Azure database to my local development server?

Examples related to azure-storage

Azure SQL Database "DTU percentage" metric

Examples related to azure-monitoring

Azure SQL Database "DTU percentage" metric