Azure High SQL Server DTU - how to analyse?

Question

Hi,

Each morning around 8am our Azure SQL Server DTU spikes to 100%. We thought it may have been the plan we were on (100 DTU's) so we increased the plan to 200 DTU's (S4), but unfortunately each morning we are still hitting the 100% DTU mark. I've tried looking in the Azure portal to see if the queries are displayed which could be affecting the performance but can only see CPU % that the queries are utilizing. Is there a better area I can run some analysis please?

Thank you

Answer 1

@T West
DTU usage is measured as a combination of CPU, IO, and Memory usage in your database, and high DTU usage could be the result of one or multiple issues with these resources.
The first step in diagnosing high DTU usage is to figure out which resource is contributing to your high usage by querying the sys.dm_db_resource_stats table

SELECT * FROM sys.dm_db_resource_stats ORDER BY end_time DESC;

Answer 2

@T West
For a single database, you can use Query Insights in Intelligent Performance. This will show you the breakdown to DTU usage across CPU, Data I/O and Log I/O. From there you can also see the query that has used the resources, clicking into the Query ID will give you the Query text.

Answer 3

Check if any index maintenance job is scheduled to run on this database at 8 am or any other resource consuming job is running at that time. When DTU reaches 100%, Azure will throttle your queries till the DTU goes below 100%. If this is index maintenance taking longer, try reorganizing the indexes instead. Update statistics regularly on tables encountering lot of DML (updates, inserts and deletes) to improve the performance of the queries on a busy database. Try to identify low period of activity for your database and schedule maintenance job at that time.