SQL Server 2019 Mirroring delays

eric_stephan-sk​ The pattern you describe (quiet tables lag, busy tables don't) fits the backoff. For SQL Server, the mirroring overview only describes it in general terms. For BigQuery and Snowflake, Microsoft spells it out: it's per table, the wait grows exponentially up to one hour when no changes are found, and normal polling resumes once new data is detected. I couldn't find a documented setting to turn it off.

To confirm it's backoff and not the gateway or the source:

• In Monitor replication, compare the table's Last completed time with the time the change landed in the CDC table.
• Turn on workspace monitoring and look at the batches per table. If ReplicatorBatchLatency is small but the batch only starts long after the change, it's the polling interval. If the latency itself is high, look at the gateway (its compute is a listed latency factor) or the source.

MirroredDatabaseTableExecution
| where Timestamp > ago(1d) and OperationName == "Replicating"
| project OperationStartTime, SourceSchemaName, SourceTableName, ProcessedRows, ReplicatorBatchLatency
| order by SourceTableName asc, OperationStartTime asc

For fact and dimension consistency, don't assume tables arrive together. Two options:

• Check freshness before the downstream step runs (Last completed, or a max modified date per table), and only process when the dimensions have caught up.
• Make the joins tolerant of late dimensions, for example with an unknown member that gets corrected on the next load.

One idea to test, not a documented setting: since polling speeds up when changes are detected, a small scheduled update on a quiet dimension might keep it out of backoff. That adds CDC and log volume, so try it on one table first.

If this solves it, please mark it as the solution.

References:

https://learn.microsoft.com/en-us/fabric/mirroring/troubleshooting

https://learn.microsoft.com/en-us/fabric/mirroring/monitor-logs