question

FabianSchmied-6155 avatar image
0 Votes"
FabianSchmied-6155 asked FabianSchmied-6155 commented

Relationship between Azure SQL database metrics and query statistics in Query Performance Insight

Consider this graph from Query Performance Insight:

125579-performance-overview-cropped.png

What's interesting about this image is that the green line shows an overall max Data IO metrics of about 20%, while the summed query Data IO is only at 0.08%.

What exactly is the reason for this discrepancy? I know that we have a query that only sometimes causes high Data IO, the rest of the time it doesn't, but that shouldn't get lost when aggregating as "sum", right? Or are averages calculated somehow despite choosing "sum" as the query aggregation?

I'd really be interested to understand this in detail.

azure-sql-database
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@FabianSchmied-6155 Thank you for posting your question. Could you please also check on the metrics report the percentage of Data IO. The difference may come from one of the query listed under the top queries. Could you please click on any row under selected queries to get the details. Did you also try to update statistics.?

Regards,
Oury

0 Votes 0 ·

@OuryBa-MSFT Thanks for the reply.

Could you please also check on the metrics report the percentage of Data IO.

The metrics graph shows max Data IO spikes of about 20-25% every hour. So, it's similar to the green line, but at a finer grained interval, thus resulting in spikes.

126822-metrics-cropped.png

The difference may come from one of the query listed under the top queries. Could you please click on any row under selected queries to get the details

I'm quite sure that the high usage spikes come from the red query. It's executed very often, and only about once or twice every hour, it will generate a high amount of data I/O.

When I click on the row for the red query, I can see this:

126833-red-query-details.png

So, it's still 0.06%. Is this an average value despite me selecting "Sum" as the query aggregation?

(If I click one of the queries underneath, e.g., in the fourth row, every line shows 0% Data IO.)

Did you also try to update statistics.?

Do you mean the SQL Server Statistics for query optimization? What would the relationship between those and the Query Performance Insight data be?

Note that my goal with this question is not to find out how to optimize my queries. I just want to understand why there is this difference between the two graphs.




0 Votes 0 ·

Reflecting about this some more, the 0.06% must probably use a different base value than the 20% do.

E.g., the query statistics might be calculated by aggregating (summing up) all Data IO within the observation period and then dividing it by the total available Data IO, whereas the metrics might be calculated by first dividing the current value by the total value, and then using the result for aggregation. That would explain the different values.

Can someone confirm this? Maybe there's even some documentation?

(Note: https://docs.microsoft.com/en-us/azure/azure-sql/database/query-performance-insight-use explains why the green line is at 20%, but I can't see an explanation for why the red bars are at 0.06% there.

0 Votes 0 ·
Show more comments

0 Answers