can anyone help me with this awful query plan?
The execution plan accesses Object7
first using a non covering index in Column7
order. It then does some key lookups on that table and nested loops joins against the other tables with the final joined resulting arriving at the TOP
operator still ordered by Column7
.
Once this has received enough rows to satisfy the OFFSET ... FETCH
it can stop requesting any more rows from downstream operators. SQL Server estimates that it will only need to read 2419 rows from the initial index on Object7.Column7
before this point is arrived at.
This estimate is not at all correct. In fact it ends up reading the entirety of Object7
and likely runs out of rows before the OFFSET ... FETCH
is satisfied.
The semi join on Object11
reduces the rowcount by almost half but the killer is the join on Object6
and predicate on the same table. Together these reduce the 9,753,116
rows coming out of the semijoin to 2
.
You could try spending some time looking at statistics on the tables involved to try and get the cardinality estimates from these joins to be more accurate or alternatively you could add OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL') )
so the plan is costed without the assumption that it can stop early due to the OFFSET ... FETCH
- this will certainly give you a different plan.