3-node Alwayson cluster: 1 synchronization and 1 asynchronous secondary replica – SQL Server 2012
We are witnessing the PageIOLatches when we read from the asynchronous secondary replica. This is mainly due to the performance of the SAN that has been limited. The hosting partner told us that this limitation can not be alleviated immediately due to hardware restrictions.
The main replica and the synchronous replica use other SANs with a higher performance. Although this situation is far from ideal, it is a temporary situation that will be resolved soon and is not the subject of my question.
When we investigate the I / O waits, we note that these occur simultaneously with an increase in the number of control pages / sec.
I had the impression that checkpoints do not occur in the secondary replicas in an AG, as explained here.
To verify this behavior, I have configured an extended event to monitor the checkpoint events in the asynchronous replica. As expected, no control points were captured for this database, nor have I found any checkpoints from other databases that match the pattern.
Next, I created the same extended event in the main replica and started a perfmon to verify if we could witness the same behavior. Here we were able to capture the control points (automatic), they occur approximately. once per minute These control points occur simultaneously with the increase of control point pages / sec. In our secondary (and primary) replica. It seems that control points are being generated in the primary and remade in the secondary replicas. This would mean that the control points occur implicitly in the secondary replicas in an AG.
I suppose it is correct that in an AG control point they are generated in the primary replica and are repeated in all the secondary replicas?
And so, if the database
TARGET_RECOVERY_TIME is not established, the
recovery interval the configuration of the primary replica will dictate the control points in all secondary replicas for these databases.