Slow Query – Incorrect Estimated Executions?

There are multiple issues here. From a runtime performance point of view, one of the main problems is the late application of the Filter:

The filtering expression refers to a column on the Provider Payment History. The optimizer is unable to push the Filter down past the Outer Join / Top / Semi Join combination, but you may be able to rework the original query specification to achieve this (while retaining correct results). If you are able to do that, it should improve performance markedly. The optimizer is limited/conservative about pushing Filters below an outer apply with top in particular.

Aside from the outer apply / top pattern not being particularly optimizer-friendly, There are other issues as well. Certainly, estimates can have a profound effect on the plan shape and operators chosen by the optimizer.

In particular, the Merge Interval + Seek pattern here is caused by a mismatch in parameter types. Specifically, the following parameters appear to be incorrectly typed, compared with the columns they are applied to:

span class="re0"> @p__linq__0
@p__linq__1
@p__linq__4
@p__linq__5
@p__linq__6
@p__linq__7
@p__linq__12
@p__linq__13

All these are typed datetime2(7) as parameters, but are associated with GetRangeWithMismatchedTypes function calls in the XML. These often result in poor estimations.

There are other things as well, but relocating the filter and addressing the incorrect types are probably the main things. Almost certainly, the execution plan and performance will be very different after making those changes.

Update

Don't be misled by the high estimated cost on the Hash Match Flow Distinct:

The 59.3% shown is the optimizer's internal estimate, used only at compilation time to make internal choices about plan operators and shape. These cost numbers never show runtime performance. The Flow Distinct in this plan simply passes on the first row it encounters, though it is executed 1,736 times overall. Even so, the actual impact of this operator on overall performance is negligible.

The poor performance of the query is now almost all due to the 5-million-odd seeks into the Provider Payments table, and to a slightly lesser extent, the Lazy Spool. Both shown below:

Check you have the right indexing on Provider Payments. It seems you need an index keyed on ProviderID, PaymentID whereas the current (FK-associated) index only contains ProviderID. This may or may not be enough to reduce the seek impact to acceptable levels; it depends on factors I can't inspect from here.

The remaining issues in that area of the plan are:

1. The incorrect choice of Nested Loops on that join;
2. Seriously inaccurate cardinality estimates in general; and
3. The Lazy Spool on the preceding join

Investigating these would require detailed analysis and full access to the database. I can say that the unfortunate choices are almost certainly due to the way the query is currently expressed in SQL, but there are no obvious quick fixes like a change in indexing. Sorry about that, but there are limits here 🙂