DTU calculation

Dmytro Lenchuk 21 Reputation points
2021-09-24T10:07:39.653+00:00

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
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Oury Ba-MSFT 16,076 Reputation points Microsoft Employee
    2021-09-24T15:54:13.537+00:00

    Hi @Dmytro Lenchuk 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://learn.microsoft.com/en-us/azure/azure-sql/database/purchasing-models
    https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu

    Please let me know if you have additional queries

    Regards,
    Oury

    1 person found this answer helpful.
  2. Alberto Morillo 32,886 Reputation points MVP
    2021-09-24T21:27:29.68+00:00

    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.

    1 person found this answer helpful.