Why is the plan showing more records read than in the table itself?
Like Sranda mentioned, it's because of the
Nested Loops Join operator that was used to join the data together. When
Nested Loops is used, it iterates through the entire collection of data that it's joining to (7 rows in this case) for the number of rows in the outer collection of data it's joining from (3 rows). So that's where the 21 comes from, 7 x 3.
Nested Loops is usually the join operator chosen when joining together small datasets (or at least when one dataset is small and the other dataset is indexed on the join predicate). The alternative options,
Hash Join and
Merge Join, have overhead that typically would be slower for small datasets.
why is the Product table using a Clustered Index Scan rather than Seek?
Because your query appears to ask for every row from the
Product table (no explicit filtering such as a
WHERE clause), so you hit what's known as the tipping point, where the SQL Server Engine thinks it's more efficient to Scan the index rather than Seek against it. And it's correct to do so, when you ask for every row in the table.
The tipping point is based on a few factors including the number of rows your query is going to return from the index vs the total number of rows that the index actually stores. With such a small number of rows in your table / index, it's probably pretty easy to hit the tipping point, unless you only queried for a single
Product at a time. But again, since you're interested in all
Products, the query plan the Engine chose is likely to be the fastest (as opposed to Index Seeks) in this case.