question

DmytroLenchuk-1118 avatar image
0 Votes"
DmytroLenchuk-1118 asked OuryBa-MSFT commented

DTU calculation

How can i calculate DTU percentage that query consumed? Are there any formulas to combine CPU, Log IO and Data IO into DTU?

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OuryBa-MSFT avatar image
1 Vote"
OuryBa-MSFT answered OuryBa-MSFT commented

Hi @DmytroLenchuk-1118 Thank you for posting your question on Microsoft Q&A.

Doing this at the query level is not trivial because you have to translate absolute values consumed by query (CPU time, log bytes, physical reads) into percentages based on limits for each resource dimension.
To determine the average percentage of DTU/eDTU utilization relative to the DTU/eDTU limit of a database or an elastic pool, use the following formula:

avg_dtu_percent = MAX(avg_cpu_percent, avg_data_io_percent, avg_log_write_percent)

Additional Info:
If you want to migrate an existing on-premises or SQL Server virtual machine workload to SQL Database, use the DTU calculator to approximate the number of DTUs needed.

https://docs.microsoft.com/en-us/azure/azure-sql/database/purchasing-models
https://docs.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu

Please let me know if you have additional queries

Regards,
Oury



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@DmytroLenchuk-1118 Please mark as accept answer above is helpful. Otherwise let us know how we can better assist here.

Regards,
Oury

0 Votes 0 ·

@DmytroLenchuk-1118 Please mark as accept answer above is helpful. Otherwise let us know how we can better assist here.

Regards,
Oury

0 Votes 0 ·
AlbertoMorillo avatar image
1 Vote"
AlbertoMorillo answered AlbertoMorillo commented

Query Performance insights shows DTU used by a query if you have Query Store enabled on the database (it is enabled by default).

135128-image.png

DTU is calculated based on those 3 elements: CPU, Data IO and Log IO. So above graph tells you if a query has high consumption of DTU and tells you which of 3 DTU components is more impacted by that query.




image.png (119.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Kudos to @AlbertoMorillo for your contribution on Microsoft Q&A. @DmytroLenchuk-1118 as Alberto mentioned CPU, DataIO, and LogIO are from Query Store, not from resource consumption data. Unfortunately, they are labeled the same way, which is a little misleading.

Please don't forget to mark as answer as accepted wherever information provided was useful.

Regards,
Oury

0 Votes 0 ·

Thank you @OuryBa-MSFT. Blessings

0 Votes 0 ·