Execution plan - reading more records than in table

Question

SQL newbie here, so apologies if this is pretty basic stuff.

SQL server 2019, Windows

2 tables - FixingHeader and Product

Simple query reading all fixing header and find related product

 select [f].[Id], [f].[FixingHeaderReference], [p].[Description] from [dbo].[FixingHeader] as f 
       JOIN [Product] AS [p] ON [f].[ProductId] = [p].[Id]

I only have 7 records in the FixingHeader database

Looking at execution plan, at surprised as some of the results.

1 - Why is the plan showing more records read than in the table itself?

Think it's to do with 3 executions, but why 3?

2 - why is the Product table using a Clustered Index Scan rather than Seek? The Product is being read using the primary key Id which has a index

Thanks!

Answer 1

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.

Answer 2

1 - because of nested loops, for every entry returned from Product, whole table FixingHeader is read. 3x7 = 21. In other words, the clustered index scan for FixingHeader was executed 3 times (i.e. it scanned the table 3x), as you mention in your post. Nested loops explanation can be found here, including animations: link

2 - It's up to decision of SQL server's optimizer whether he'll dive-bomb into particular index section and starts reading from e.g. middle of the index or whether it's cheaper to read the whole index from start to end, actually clustered index is the table itself in particular order dictated by the index columns. It's a myth saying that seek always good, scan always worse. Seek only indicates that SQL server knew where in the index to start reading data.