Incorrect cardinality estimation of 1 for table
One of the problems I'm seeing and couldn't find a way to solve is that the RID Lookups and Index Seek operations on Object12 have a cardinality estimator of 1 row, but they actually have 11.7k rows. Due to this, I think that the Nested Loop operator is actually the wrong operation to be performed here and a merge would be more appropriate, but I can't figure out a way to make the Optimizer choose this kind of plan.
I've checked and updated my stats for this table, but things haven't changed. I'm not sure what I can look at next. Any suggestions are greatly appreciated.
This says that estimate row count was 3609 but actual is 11701 which is much different than 1 row you said it estimated. I think what you are referring to is "1.0 rows per iteration" message on next to the red mark. This simply means that nested operation was done 1 row at the time, not that 1 row was estimated.
Your problem in this query comes from the use of multiple heaps. By placing a non-clustered index on a heap SQL Server is able to get most of the data it needs but if it is missing any columns it does very costly RID Lookup operation.
My recommendations are as follows.
- Create clustered indexes on all HEAPS
- If plausible modify non-clustered indexes to become "covered" indexes. In the case of RID Lookup on Object11, only 1 extra column is included. In the case of RID Lookup on Object12, only 2 columns are included. If these columns are added to the index with INCLUDE keyword you could avoid RID lookups.
Lastly, it is always the best to run query from within Plan Explorer to allow application to gather extra statistics to get actual costs of the operations instead of estimates that come from SSMS.