search

MicrosoftDocs / azure-docs

Open source documentation of Microsoft Azure
https://docs.microsoft.com/azure
Creative Commons Attribution 4.0 International
10.28k stars 21.47k forks source link

Which metrics? #40014

Closed dcbrown16 closed 3 years ago

dcbrown16 commented 5 years ago

What metrics exactly are available through Intelligent Insights logs? In particular, as shown here Managed Instance databases do not support the basic metrics or basic logs, but they do support Intelligent Insights. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-metrics-diag-logging Can we get info such as per DB IOPS/CPU/memory for Managed Instance from these Intelligent Insights logs?

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

danimir commented 5 years ago

@dcbrown16 - Intelligent Insights are described on this page in detail. Intelligent insights are not raw metrics and they do not provide raw information you are looking for. This is a built in AI that can automatically troubleshoot performance issues listed on this page. Log entry is produced only in case a performance issue is detected, otherwise if all is running good, no log entries are provided.

In terms of your other question, Managed Instance (MI) actually provide IOPS/CPU/memory per instance through SQL Analytics which is documented on this page. These metrics are just not exposed in the portal (which is what I believe you are referring to), but you can consume them through SQL Analytics instead. In terms of your question for these metrics per database, that is not possible nor available as Managed Instance does not govern databases. This means that all databases can use full resources of the instance, and compete for it, hence no CPU/IOPS/memory as a concept of measure exists for MI databases. You would therefore monitor MI performance per the entire instance, hence IOPS/CPU/memory exists per instance, consumable through SQL Analytics at this time.

For your monitoring needs for MI, you can also consider using CTP version of SCOM MP for MI which we have recently released, or perhaps some 3rd party solutions (which would be based on pulling DMV stats).

Thanks.

dcbrown16 commented 5 years ago

Thanks @danimir! I understood that the platform metrics are offered per instance, not per DB in MI. This Intelligent Insights page (that I reported the issue on) has a "Metrics" section - what are we getting there? https://docs.microsoft.com/en-us/azure/sql-database/sql-database-intelligent-insights-use-diagnostics-log#metrics DurationIncreaseSeconds and the other two examples that are mentioned at that link don't match any of the platform metrics offered for MI that we see here. https://docs.microsoft.com/en-us/azure/azure-monitor/platform/metrics-supported#microsoftsqlmanagedinstances And am I understanding correctly that these platform metrics are NOT shown through Azure Monitor or the SQL MI portal blade? I'm assisting a customer with guidance but unfortunately I don't have an MI at hand myself and I didn't see any screenshots of the Metrics interface.

danimir commented 5 years ago

@dcbrown16 You are making an assumption that instance level metrics and intelligent insights "metrics field in JSON" have a relationships - they do not. You cannot compare them the way you are describing as they are not related. We have also documented instance level metrics and intelligent insights as separate pages purposely to make the separation between them. Intelligent Insights is a separate product, not available in the portal, and only consumable through SQL Analytics. Think of it as a completely separate system from the standard raw instance metrics. Intelligent Insight "metrics" in JSON is populated with 14-15 detectable performance issues that are documented on this page. This is not a raw metric, but an assessment from AI if there is a performance issue on your database, described in a human language. That's it. All available telemetry is already documented and there is nothing more available other than what is already stated. Hope this helps. Thanks.

dcbrown16 commented 5 years ago

Sorry @danimir, I understand that platform metrics and Intelligent Insights are not the same. I'm having trouble understanding the "Metrics" section on the page I reported this issue on, then. That section gives these three possible values. issue metric type – possible values: DurationIncreaseSeconds, CriticalErrorCount, WaitingSeconds I don't see how that lines up with the categories of issues on the other page you linked. The closest I see on that page is the intro sentence stating "Intelligent Insights automatically detects performance issues with SQL Database and Managed Instance databases based on query execution wait times, errors, or time-outs." So should we understand that all of the 14-15 performance issue categories also include one or more of these three metric values? Are the "metrics" to be seen as supporting evidence for how Intelligent Insights concluded what the performance issue is, like a high value of WaitingSeconds leads to Increased Wait Statistic (just as an example)?

danimir commented 3 years ago

Intelligent insights include a mix of metrics to determine performance, amongst which the ones you mention as well, and we use Machine learning. We are still prototyping this and are not documenting the algorithm behind Intelligent Insights.

danimir commented 3 years ago

please-close