The amount of rows flowing into the nested loop join
Im confused because looking at the top row where i have highlighted, then it seems that 29,786 rows keep on flowing through the nested loop ?
Yes, this means you are scanning all 13,046,268 rows from Object2 and performing a LEFT JOIN to the Object42 table as defined in the ON clause of the LEFT JOIN Object2 Object33 ON Object33.Column2=Object42.Column51. Of the millions of records scanned, the 29,786 records that match and added to the working table and passed along to the next condition in this case, the next Left Join.
The 29,786 records flow through most of the tables from start to finish.
Based on the large variance between estimated and actual rows in many of the Table Scans, Index Seeks, and Clustered Index Seeks I would recommend checking when the Statistics were last updated on these tables. Out of date statistics can result in these large variances.
There is a predicate on the join (ScalarString224) that filters out all the extra rows, so only one output row per input driving row is produced by the join:
The optimizer chose nested loops join driving a full table scan, with a predicate on the join, based on the low expected number of rows (38 on the outer join input, 16,490 from the table scan). Neither of these things were remotely accurate at run time, so the plan is not a good one.
The query is large and has many joins. You may need to break it up to get better cardinality estimates, or use hints. There are many possibilities, including changing the indexing on Object2 so the optimizer chooses an indexed loops join for the predicate. A hash join would work as well.