Azure SQL Database - named replica DB performance problem

Question

Hello everyone,

I have one performance problem and one cost problem of our Azure SQL database and it's replica.

• Performance problem:

We have a primary Azure SQL database and also a secondary read-only named replica database, which have both relative high pricing tier( Hyperscale - Serverless: Gen5, 18 vCores). For the write operation we use the primary database (write data in). And for big data analysis application we use the secondary replica database (read the data from it via SQL query).

For any reasons the performance of the secondary database is not stabile. Sometime the query responses really quick. But most of the time the query take really long time to response. And the primary database does not have such problem, most of the time it responses relative quickly. Anyone know why? What can go wrong with the secondary database or it's configuration?

• cost problem:

I have a question regarding the space of the database: the two database have the same used and allocated space. used space is around 1 TB and allocated space is around 2 TB. Because we have the primary and secondary database as Hyperscale database, we can not shrink database to reduce the allocated space? How can we reduce the allocated space to reduce the cost for Hyperscale database? We use only 1 TB used space but pay for the 2 TB allocated space.

Any help will be appreciated.

Best regards

Narsu

Answer 1

Agree with Geetha suggestion to raise a support incident for both performance issue and allocated space as shrink functionality is currently not supported in Hyperscale - refer https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale?view=azuresql#known-limitations. However, I will still recommend raising a support incident as you may get a solution for both. In addition to that could you review below suggestions:

1. Validate the compute/SLO size of your Named replica and provision the right compute size required by your analytical queries. Buffer Pool and local SSD cache/RBPEX cache on compute node to retain hot data pages are allocated based on the compute/SLO size you have provisioned. When a read is issued on a compute replica, if the data doesn't exist in the buffer pool or local RBPEX cache, a getPage(pageID,LSN) function call is issued, and the page is fetched from the corresponding page server. Reads from page servers are remote reads and are thus slower than from local RBPEX reads.
2. Follow general performance tuning methodologies by monitoring query waits on a Named replica using standard DMVs and take necessary action.
3. I understand you have provisioned a Serverless compute on primary, if your workload is predictable on Named replicas, consider changing it to provisioned compute with appropriate compute size so that temporary statistics created on Named replica to optimize the analytical query can be retained.