How to optimize this plan? Lot of difference between estimated and actual rows.

Max 2015-04-01 12:33:18

SQLkiwi 2015-04-01 13:39:36
It's possible (though impossible to confirm using the anonymized plan supplied) that the estimates were computed using a different set of parameter values. If that is the case, try using an OPTION (RECOMPILE) hint to see if that improves the situation. Depending on how often the query runs, that might even be an acceptable "fix". See this article on parameter sniffing for more background.

You should also review the indexing on these tables to reduce or eliminate the Key Lookups and Eager Index Spools where possible.