Monitor queries on secondary database when read scale out is enabled in Premium tier
Monitor queries on secondary database when read scale out is enabled in Premium tier. Currently we can only find out if query is getting executed on secondary database by using SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability') but there is no way to identify in Query Performance Insights.
Mark Freeman commented
I see 100% CPU use in a geo-replicated secondary used for read-only queries, but have no way to determine what query was responsible or where it was coming from? Unacceptable.
Hey microsoft, any updates on this matter?
Sa'Ed A'Rafat commented
Having telemetry data available on the read-only replicas in the portal is needed but more importantly we need it as part of the diagnostics stream as described here: https://docs.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure?tabs=azure-portal
This helps us configure alerts.
There should be some way to trace down execution details like query plan, resource consumption etc. for the history query ran on read-replica, currently, query store captures the history for the queries running only on the read-write instance only which is a big worry in case of the business-critical database to utilize read replica since I'm finding it almost impossible to get the execution plan for a query run in past(before few days).
Do we already have some way to achieve this and get the actual execution plan for a past run which is highly needed for the debugging of a critical issue?
Chris Jones commented
This is really vital. We spent ages debugging a problem we were having with very slow SQL queries, but all the metrics in the Azure Portal looked fine.
In the end we discovered that the read scale-out replica was running at 100% CPU but it's completely hidden and you need arcane SQL commands to see its metrics. This needs to be surfaced in the UI and needs to allow metrics/alerts against it
Oscar Pena commented
Is this still the case?
I am agreed
Clement Gutel commented
For hyperscale this gets really important to be able to monitor read nodes load so that we can tweak how many replicas to enable.
[Deleted User] commented
Agreed. Have to log into the secondary to get any metrics.
Also, the portal metrics tool should be able to select between primary and readonly replica.