Where's the Rest of DTU?

Question

I am looking at my Azure Sql Server performance:

Notice the % in the red rectangle.

Now when I go to the overview and look at my server for the past 24 hours I see this:

I'm having trouble squaring away these two graphs. One says that my CPU utilization is under 1.3% but another is saying up to 63% DTU utilization. This is a marked difference and is causing me some confusion. If my server is using 63% DTU and only 1.3% of that is being used by queries in CPU, then what is the other % being used by in my DTU utilization?

Any guidance/insight towards resolving my confusion would be appreciated.

Answer 1

My recommendation is not fully trust the DTU graph, it has been subject of a good number of complaints in the past. Use T-SQL to get better inside:

SELECT * 
FROM sys.dm_db_resource_stats
WHERE database_name = 'AdventureWorksLT' AND           
start_time > DATEADD(day, -7, GETDATE())
ORDER BY start_time DESC;

Use Query Store also.

select top 10 q.query_id, p.plan_id, qt.query_sql_text,
rs.count_executions,
CONVERT(NUMERIC(10,2), (rs.avg_cpu_time/1000)) as 'avg_cpu_time_seconds',
CONVERT(NUMERIC(10,2),(rs.avg_duration/1000)) as 'avg_duration_seconds',
CONVERT(NUMERIC(10,2),rs.avg_logical_io_reads ) as 'avg_logical_io_reads',
CONVERT(NUMERIC(10,2),rs.avg_logical_io_writes ) as 'avg_logical_io_writes',
CONVERT(NUMERIC(10,2),rs.avg_physical_io_reads ) as 'avg_physical_io_reads',
CONVERT(NUMERIC(10,0),rs.avg_rowcount ) as 'avg_rowcount'
from sys.query_store_query q
JOIN sys.query_store_query_text    [qt]       ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
where rs.last_execution_time > dateadd(hour, -1, getutcdate())
order by avg_cpu_time_seconds

Read here for more information.

Answer 2

Hi,

DTU is not purely CPU, it consists of CPU, I/O and memory consumption. This documentation explains it more in-depth: [https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu?view=azuresql

The difference between the graphs is most likely that, the queries are more I/O and/or Memory intensive than CPU.

The "Compare Service Tier" paragraph gives a better insight on what the available resources are in your selected tier:

[https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu?view=azuresql#compare-service-tiers

Also from the documentations "Determine DTU Utilization" paragraph a quick calculation that helps with determining DTU:

avg_dtu_percent = MAX(avg_cpu_percent, avg_data_io_percent, avg_log_write_percent)

[https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu?view=azuresql#determine-dtu-utilization

BR,

Peter