question

Rocky420 avatar image
Rocky420 asked ·

Metrics for SQL Azure read -only scale out

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
1 comment
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@rocky420 Please let us know if you need further help. If the information helped you, please accept is an answer.

0 Votes 0 · ·
NavtejSaini-MSFT avatar image
NavtejSaini-MSFT answered ·

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




capture.jpg (163.2 KiB)
Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MalleswarReddy avatar image
MalleswarReddy answered ·

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://docs.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://docs.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

1 comment Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Malles for pointers but this is still not clear . If DTU are shared between primary and ready only database or both of them will have same DTU

  1. Primary and read only : 100 DTU shared by them or

  2. Primary 1000 DTU and read only - 1000 DTU .

which one is correct ? As per the document https://docs.microsoft.com/en-us/azure/azure-sql/database/read-scale-out, we get read only feature free for premium , so i want to know if am gonna pay extra for DTU if option 2 is correct ?





0 Votes 0 · ·