Metrics for SQL Azure read -only scale out

Rocky420 21 Reputation points
2020-06-08T06:43:16.91+00:00

Hello Team,

we have enabled read only scale out feature of SQL azure so that we can redirect our read only traffic to this read only DB and utilize high through put and separate the write and read traffic. Recently , we are seeing some connection time out issue for read only DB calls from service and we are unable to figure out what is the issue ? like do we have less DTU , high log IO for read only

couple of questions

  1. If primary DB is in P3 and 1000 DTU and if we enable read only scale out DB , then how many DTU will be allocated to read only ? will these be shared or not ?
  2. How can I increase DTU for read only ( I do not see any option for the same in portal )
  3. How can I see the DB metrics ( DTU, log , I/O ) for read only DB to identify whether we are overutilizing or underutilizing database

Any pointers are highly appreciated

P.S : unable to add sql azure as do not see any other azure tag

Azure SQL Database
Accepted answer
  1. Navtej Singh Saini 4,216 Reputation points Microsoft Employee
    2020-06-16T00:21:14.303+00:00

    Hi Rocky

    You are referring the correct document for your question. It answers both of your questions:

    10202-capture.jpg

    So according to above the configuration that is there for Read Scale out is:

    >Primary 1000 DTU and read only - 1000 DTU .and this is at no extra cost.

    Hope this helps clarifying the answer.

    Regards
    Navtej S

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Malleswara Reddy, G 1,631 Reputation points
    2020-06-08T07:48:06.2+00:00

    Hi,

    If primary DB is in P3 and 1000 DTU and if we enable read only scale out DB , then how many DTU will be allocated to read only ? will these be shared or not ?
    Check Q & A -- https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale-frequently-asked-questions-faq#read-scale-out-questions

    How can I increase DTU for read only ( I do not see any option for the same in portal )
    -- No, it is not possible. Please check Q & A in this link -- https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale-frequently-asked-questions-faq#read-scale-out-questions

    How can I see the DB metrics ( DTU, log , I/O ) for read only DB to identify whether we are overutilizing or underutilizing database
    --- AFAIK, this option is currently not available. Please add your feedback here. https://feedback.azure.com/forums/217321-sql-database/suggestions/34337935-monitor-queries-on-secondary-database-when-read-sc
    But you can try out --
    Monitoring and troubleshooting read-only replica
    When connected to a read-only replica, you can access the performance metrics using the sys.dm_db_resource_stats DMV. To access query plan statistics, use the sys.dm_exec_query_stats, sys.dm_exec_query_plan and sys.dm_exec_sql_text DMVs.

    Regards,
    Eshwar