Not understanding Azure SQL DB high DTU's.

chrisrdba 381 Reputation points
2022-05-10T18:04:36.733+00:00

Greetings.

On the azure portal, looking at the Overview and seeing that the DTU Percentage (max) has hit 100% a lot over the last 24 hours. However, when looking in Performance Overview or Query Performance Insights I cant find anything that comes anywhere near 100% for the Top 5 queries no matter how I sort, etc (CPU, IO, or Log). In fact if I total up all queries we're looking at maybe 5% for any of those columns.

I should also mention that I only have 1 DB on this guy.

So my questions are:

  1. How/ where is memory listed?
  2. Is there something else I should be looking at?
Azure SQL Database
0 comments No comments
{count} vote

Accepted answer
  1. Alberto Morillo 33,421 Reputation points MVP
    2022-05-10T20:05:29.607+00:00

    1) Azure SQL Database is always using 100% memory by design.

    2) Where else to look at?

    The following query tells you which resource(s) consumption is(are) creating DTU spike:

    SELECT   
        (COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'  
        ,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'  
        ,(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'  
    FROM sys.dm_db_resource_stats  
    

    If you change the aggregation types to max you may have a different perspective:

    200733-image.png


0 additional answers

Sort by: Most helpful