1

I have a monitoring query that populates a dashboard. It was created to monitor the transactions that occur on the primary replica. To avoid overloading the Primary Replica, I created a Named Replica. However, when I execute the query on the Named Replica, the execution time drastically increases.

Upon reviewing the query's state during execution, it showed "PAGEIOLATCH_SH".

Based on the synchronization process that occurs between the Named and Primary replicas, I consider that the bottleneck may be happening at the "Log Service", which synchronizes both databases. I'm not sure if the hint "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" may have a counter-effect when the Named Replica reads from the "Log Service", as it has to apply both the changes occurring on the Primary and the uncommitted transactions.

I executed some tests on a Cluster environment and it worked fine but I know the synchronization is different vs Azure Cloud

additionally have tried to use an HA Replica and the Results were the same, at this moment using the query directly on the Primary replica is not a viable one but will consider it if no answer is found for this behavior.

Edit:

Is there a way to review the telemetry of Hypescale replication? if not, how can I monitor the Primary replication through the Log Service?

otherwise, how does the "PAGEIOLATCH_SH" work on Azure replication, As disks are not monitorable how can I troubleshoot the issue happening?

here is the documentation I have reviewed: Hyperscale architecture https://learn.microsoft.com/en-us/azure/azure-sql/database/hyperscale-architecture?view=azuresql#hyperscale-architecture-overview Identify IO performance issues https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/monitoring-with-dmvs?view=azuresql

1
  • You should add your question as well, right now you only set the stage for the issue at hand, like the beginning star wars text scroll Mar 2 at 10:21

2 Answers 2

0

The secondaries use Read Committed Snapshot Isolation so that the log can be applied to the data pages whilst still providing access to the data for those who connect with read committed transaction isolation level, you however are requesting to use read-uncommitted

0

Am experiencing the same problem...

Azure Hyperscale Named replica - getting mostly PAGEIOLATCH_SH and some WAIT_REDO_CATCHUP waits when there is even the slightest bit of load on the primary.

Also tried ApplicationIntent=ReadOnly against the primary (which would amount to the same thing as reading from the replica).

Query runs instantly against the primary without the connectionString intent - 5s-30s against the replica.

Running against EC2 AlwaysOn or local AlwaysOn cluster against a replica works seamlessly - technically not the same architecture but same intent. Have we gone backwards?

I did find this question: https://stackoverflow.com/questions/59887995/azure-sql-server-read-scale-out-performance where the author suggested that Microsoft had to change the replication model from parallel to another model to fix their issue - but not sure if it is related as I cannot see the spin locks that could be affecting this.

Anyone have any ideas?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.