Metrics for read-only replicas

[stephenjust]

It does not appear that queries to read-only replicas show up in a SQL database's metrics (CPU, I/O, query analysis, etc). For a read-heavy workload this means that we cannot see if we are close to hitting resource constraints. Is there a way to view these metrics in the portal for read-only replicas?

---

Document Details

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

• ID: 7a2961d1-4448-11b7-2dc2-3b738bd70939
• Version Independent ID: 206eb022-25c4-66d7-7285-0d53a84956e3
• Content: Azure SQL Database - read queries on replicas
• Content Source: articles/sql-database/sql-database-read-scale-out.md
• Service: sql-database
• GitHub Login: @anosov1960
• Microsoft Alias: sashan

[angoyal-msft]

@stephenjust Thanks for your feedback. We are investigating this issue actively and will get back to you soon.

[angoyal-msft]

@stephenjust Sorry for the delay. I have created a new read-only replica for my db and when I am running any query in read only replica. I was able to see the metrics for the same in read-only db. Below is the screenshot for the same.

Please confirm if this is what your concern is. or please elaborate if I am not able to understand your issue.

[ColeAtRecRoom]

@angoyal-msft I believe @stephenjust is talking about the "Read scale-out" feature of premium/business tier Azure SQL databases, which is found under "Settings" > "Configure" in the Azure portal when a SQL database is selected. I'm not aware of any way to view metrics for the read slaves when using that feature.

[stephenjust]

@ColeShepherdAG is correct, I was talking about getting metrics from the read replicas when read scale-out is enabled.

What we observe is that in the portal, statistics are only visible from the write master. If we set ApplicationIntent=ReadOnly, we do not observe any resource consumption from those queries in the Azure portal.

[ColeAtRecRoom]

@stephenjust One (limited) workaround in the meantime is periodically SELECTing from sys.dm_db_resource_stats while connected to your database with ApplicationIntent=ReadOnly.

[angoyal-msft]

We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion.

[ColeAtRecRoom]

@angoyal-msft I think we should get a response to our latest comments before the thread is closed...

Have you confirmed that there are no metrics for only the database created using the "Read scale-out" feature of premium/business tier Azure SQL databases (not including the primary read/write database)? If so, are there any plans to add these metrics to the Azure portal? Can you at least add information about the sys.dm_db_resource_stats table and ApplicationIntent=ReadOnly to this article for other readers?

[angoyal-msft]

@ColeShepherdAG Thanks for your useful suggestion. @anosov1960 could you please add the required information in the document.

[dmarlow]

Having round-robin to the read replicas as well as metrics would be a great boost for performance. Please add this.

[vferris]

I want to throw my 2 cents in here as well regarding metrics for read only replicas. I would love to take advantage of this as a cost saving feature but currently cannot do so. We rely on metrics and Azure SQL Analytics for alerts and day over day statistics. Right now I don’t see how we can implement this feature with no visibility into the performance of it.

[dmarlow]

You can get metrics, they're just not provided to you via the portal and come with all of the typical bells and whistles (like alerting, etc.). I've been looking at setting up some monitoring tools of my own since what Azure provides isn't sufficient anyways (like seeing the relative DTU utilization at the application/program level). If your monitoring tools connect using the ApplicationIntent=ReadOnly parameter, you can execute sp_who2 or other SPs that you have to see information specific to the replica. Then, automate calling that SP, gathering results and displaying. I'm looking at this for some added monitoring: https://sqlwatch.io/

[vferris]

I'm looking at this for some added monitoring: https://sqlwatch.io/ How do you get around the lack of a sql agent?

[dmarlow]

I'm still investigating, but it's my understanding that you just need to invoke a few stored procs periodically. If that's the case, that can be automated in many number of different ways. From a script on some machine to Azure Automation and many things in between.

[smidley]

@angoyal-msft Can you please re-open this issue since it's not resolved? There's still no way to get metrics from scale out read-replicas via the Azure Portal.

[alb-xss]

Absolutely needed. I don't want to rely on workarounds for such an important feature like monitoring. Metrics should be available also for read scale-out DB replicas.

[NavtejSaini-MSFT]

@ejjpi We have reopened this issue and assigned to our author. @anosov1960 Please check this issue and provide guidance.

[itshari]

@NavtejSaini-MSFT Any updates on this issue? Even we are blocked on querying the metrics of SQL read replica using the Monitoring API.

[chrisolzi]

@angoyal-msft, Any updates on this? This is a major miss that we need ASAP.

[blaawolf]

Are you planning to include read scale-out DB replica metrics in the main dtu_used metric or at least add a separate one for only the replicas (the latter might be actually more useful)? We would like to continue utilizing the metrics provided by Azure and use the replicas.

We do not want to run out of DTUs without knowing it.

[alb-xss]

I think this issue won't ever be properly addressed in this repo: this is not about a missing part of the documentation but the lack of a critical monitoring feature on the Azure portal. Can anybody from Microsoft move/escalate this issue to the proper area/team?

[stephenjust]

I've escalated to the team which owns the Azure SQL - Azure Portal integration.

[Nikos-K]

@stephenjust Any updates on this?

[julieMSFT]

reassign:julieMSFT

[julieMSFT]

@Nikos-K @stephenjust Thanks for contacting Microsoft! From my understanding, the issue you're describing seems to be an issue with the product, is that correct? If so, unfortunately, this form of contact is intended explicitly for reporting issues with the documentation, and not for the product. To get further assistance with your issue, we'd recommend engaging the community, or opening a ticket with Microsoft Support. More information can be found here: https://docs.microsoft.com/en-us/sql/sql-server/sql-server-get-help?view=sql-server-2017.

If you're looking to provide suggestions or ideas for improvement for the product, you can do so at https://aka.ms/sqlfeedback.

I am now Closing this Issue 24239. The @ notification mechanism continues to work even after an item is Closed, in case further communication is needed.

[julieMSFT]

please-close

[JeremyWeir]

Does everyone agree that this is the correct product issue that relates to what is being discussed here? https://feedback.azure.com/forums/908035-sql-server/suggestions/32899126-enable-query-store-for-collection-on-a-read-only-r

(if so, please upvote both that UserVoice suggestion and this comment)

[kroymann]

@JeremyWeir While the issue you linked is related to this one, they do not seem to be the same problem. As I understood it, this issue here was focused on exposing metrics from the read scale-out replicas that could then be used like any other Azure metric (ie: building charts, creating automated alerts, etc), which is related to but separate from having the Query Store enabled for read-only replicas. That said, I've gone and upvoted your issue as it is equally as important to me as this one is.

<rant> I'm also disappointed that this issue was once again closed by simply saying "this isn't a documentation issue, it's a product issue" but without taking the initiative to forward the issue to the appropriate product team within Microsoft and then inform this thread how we could track that new issue. I worked for Microsoft for 14 years before leaving 4 years ago to start my own company, and while I am extremely happy to see the recent shift toward open development processes that give customers a direct channel to debug problems, provide feedback, and track progress on the issues they care about, I am still quite disappointed with the way that issues routinely get dismissed as "not my area of concern". This particular issue is extremely important for anyone who wants to leverage the read-slave replicas which are a key selling feature of the top-tier, most-expensive option for Azure SQL. The lack of empathy for the customer shown here is a bit depressing.</rant>

[JeremyWeir]

@kroymann Good point, I think I came to assume that Query Store was what powered the metrics we see for Azure SQL databases, but whether or not that is the case, you're right, any metrics we can get outside of Query Store would be helpful.

[Mike-Ubezzi-MSFT]

@JeremyWeir @kroymann We are learning how to best manage this newer channel. The original intent and the one it still serves today is to address documentation related topics, both enhancements and corrections that need to be made. This should also include issues where the documentation and the product do not align. As a specific product evolves over time or as changes are made (to the portal as an example) and where the documentation needs a correction, this channel has been very useful in making us aware these issues exist. As for feature requests, this channel is not intended to support these types of requests but given the ease of making suggestions to a specific component or feature through this channel, as the documentation is topic based and a conversation can take place with regard to a specific feature, it is helpful to bring together that UserVoice entry with a specific documentation topic. By detailing the UserVoice link to a feature request, it is the desire of the product group that your feedback is detailed in that forum. I included some relevant UserVoice entries here incase the desired functionality is of any interest.

Round-robin read replicas and provide performance metrics of replicas in portal

Provide insight into the replication lag between Azure SQL Database primary and Read Scale-Out replica

We have implemented some processes to escalate issues that fall in that gray zone that are pure product issue, where the document is correct but the issue is more product related. In these cases, the doc issue will still be closed but the issue will be tracked internally.

We greatly appreciate the feedback we receive and your assistance with raising our awareness to tutorials and samples that simply do not work as designed.

[JeremyWeir]

This one is also related and relevant https://feedback.azure.com/forums/217321-sql-database/suggestions/34337935-monitor-queries-on-secondary-database-when-read-sc

[specialforest]

The above feedback link is gone. I still don't see metrics when using read scale-out. I guess the feature is still not there.

[Suchiman]

@specialforest microsoft likes to break links all the time nowadays but i think this link now lives here https://feedback.azure.com/d365community/idea/58a26c28-3225-ec11-b6e6-000d3a4f0f84