The amount of rows flowing into the nested loop join

Pearljammer1 2016-02-24 17:07:06

Where I have highlighted the 2 nested loops and table scans, does it mean that 13,046,268 rows are flowing into the top left nested loop ?
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 ?

SDyckes2 2016-02-24 20:41:34
If I understand correctly, you are talking about the Table Scan on Object2. This table scan has an estimated record count of 16,490, but an actual count of 13,046,268.

alt text

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.

SQLkiwi 2016-02-26 11:27:55
Yes, 13-million-odd rows (in total) arrive at the nested loops join.

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:

alt text

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.