Sql-server – Always On: replication over cross server SQL instances

Swiped from my answer on StackOverflow, only to prevent others from spending effort on the same type of answer.

Personally I think this will work a lot better than transactional replication, though I haven't done any formal comparisons of the two in a true migration scenario. I know that with the amount of troubles folks have with transactional replication, compared to even standard mirroring (and this is an upgrade of that), you are certainly bound to have fewer problems.

The biggest boon is that the secondary can be marked as read only - so you can run all the reporting off of it that you want, and it won't affect the mirroring at all. You just need beefier tempdb (since it essentially uses rcsi to do this).

Of course you do need to be aware that both sides of the AG need to be fully licensed in order to use the replica for read-only operations. And both sides need to be running on specific versions of Windows (Enterprise or better on 2008 R2 and lower; Standard or better on 2012 and above) because they require failover clustering - the SQL instances in AGs don't need to be clustered, but they need to be sitting on top of that infrastructure in the OS.

From the SQL Server query optimizer's point of view, there is not much to choose between the parallel and serial execution plans in this case.

In general, the optimizer's cost model reduces the CPU cost (not the I/O cost) of operators in a parallel plan in proportion to the estimated degree of parallelism available. This CPU adjustment explains why the optimizer ever chooses a parallel plan (which will generally consume more resources) over a serial plan.

Unfortunately, the cost model does not apply this CPU reduction to the inner side of a nested loops join. It makes no sense to me (because the inner side still uses parallelism efficiently), but I didn't design the cost model.

Anyway, because the majority of the CPU cost in this execution plan is associated with the Clustered Index Scan (for which CPU reduction does not apply), the choice between serial and parallel is a close one. In broad terms, to be selected, a parallel plan must save enough using the local/global Stream Aggregate to compensate for the extra exchanges (Distribute and Gather Streams). The costs involved in that decision depend sensitively on the distribution of row values, as well as the number of rows. With relatively few rows and low-CPU operators, the trade-off can easily go either way.

In short, this query suffers from a debatable design choice applied to the costing of parallel nested loops joins. You can force the selection of a parallel plan using a plan guide, or by using the undocumented trace flag 8649. In SQL Server 2016 SP1 CU2 onward, you can also use the undocumented ENABLE_PARALLEL_PLAN_PREFERENCE hint.