I have a query where I need to cross join a small table with 5 rows to a table with roughly 31k rows. The execution plan SQL Server came up with does index scans in both tables and joins them with Nested Loops (INNER JOIN). However, when checking the execution plan, I noticed that the scan on the smaller table produced 5 x 31k = 155k rows for the following step. Does that mean SQL Server is scanning the index of the smaller table 31k times?
Here is a small reproduction of the problem I'm having:
CREATE TABLE A (id INT PRIMARY KEY); CREATE TABLE B (id INT PRIMARY KEY); INSERT INTO A(id) VALUES (1), (2), (3), (4), (5); INSERT INTO B(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9) SELECT * FROM A CROSS JOIN B
My question is whether the index scan on the smaller table is performing multiple I/O operations, one for each row on the bigger table, as opposed to doing it only once and having its result being fed to the following step only once.