I'm working on optimizing some queries in a database I've inherited. I'm not permitted to disclose the query, but I can present an anonymized version of a query plan that is showing some very strange behavior (to me):
My question is about the 'Clustered Index Scan' denoted (1) in the images. As I understand it, the 'Clustered Index Scan' highlighted in yellow doesn't have any dependencies. The operation doesn't have any predicates (although I don't show it here) and it's sibling in the tree (2) is executed the same number of times as the number of rows (1) returns -- I take this to mean that (1) is executed first, and (2) is executed row by row for the join (which makes sense, since the join is a 'Nested Loop').
Here's the issue: The 'Estimated Rows' for (1) is 46. The 'Actual Rows' is 283972, which is the number of rows in that table exactly...which makes sense since there's nothing to trim it down. I know the query is very complex (4 levels of nested views), but how in the world does SQL Server get this wrong? Why isn't the estimate for the number of rows that come back in an Index Scan with no predicate equal to the number of rows in the table?
I've updated all of the relevant table statistics with no success. I've also noticed that reducing the nesting level of the queries has the effect of the problem occurring less often, though the results are 'unstable'. There are a number of queries that reference this view, and if I partially reduce the nesting of this view, a few queries may be fixed, but it may break a few more.