CODEWITHSUNDEEP
azure-sql-database
Matthew
Matthew

Reputation: 500

Azure Database high DTU - High IO Avg

I'm trying to work out the cause of the high DTU on a database (its rank is S2 that is also geo replicated). On a server which is unsure if its V12 or the older (different problem).

Friday last week and this Friday we have a spike that looks like this:

High DTU

Looking at the resource stats:

SELECT TOP 1000 * FROM sys.dm_db_resource_stats ORDER BY end_time DESC

avg CPU kicks around 3-5% during the peak but most significantly the avg_data_io_percentage is roaming about 72% - 90%

How can I track down the IO further?

Query Performance Insight is quite useful but execution count and cpu could be misleading in this case?

TOP 5 queries per CPU consumption Top 5 queries by cpu consumption 24 hours

top 5 during that odd period:

Top 5 queries by an hour

Are the likely offenders the queries that that appear differently in those top five?
Is there a better way to see the IO graph or data? Am I looking at the wrong thing? :D

Thanks in advance.

Upvotes: 0

Views: 1681

Answers (1)

Torsten Grabs
Torsten Grabs

Reputation: 481

You can use SSMS and the built-in reports for Query Performance Insights/Query Data Store to look at IO-intensive queries. I suggest connecting to the database using SSMS and looking at the most resource intensive queries using the logical reads, logical writes, and physical reads metrics. You should find your offender in one of these.

Thanks, Torsten

Upvotes: 1

Related Questions