During these last days we received multiple questions abouts how to monitor the performance and the queries that were executed in the secondaries replicas using in the connection string ApplicationIntent=READONLY with Read Scale Out feature.
In this article we explained time ago that is a Read Scale Out for Premium databases and in this article we would like to show you some alternatives to monitor the queries and performance of your database.
As you know Query Data Store, Extended Events, SQL Profiler and Audit features are not supported on the read-only replicas, so the way to monitor the replicas will be to use the available DMVs, such as sys.dm_db_resource_stats. Using the DMVs seems to be the best option for these cases, further information can be found in the link below: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-read-scale-out#monitoring-and-troub...
In another hand, using this DMV: select * from sys.dm_db_resource_stats you could review the resources utilization. Also, if you need more information, in this URL: https://github.com/JMNetwalker/MonGeoAzure you could have more ways to collect the data.
A lesson learned working with ApplicationIntent=ReadOnly, I found:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.