Nested-Loop-Join: How many comparisons and how many pages-accesses?

We can force SQL Server to do exactly this and see what actually happens.

R has 1000 tuples and 100 page-accesses = 10 tuples/page = 806 bytes/tuple.
S has 50 tuples and 25 page-accesses = 2 tuples/page = 4030 bytes/tuple.

These are the tables:

drop table if exists dbo.R;
drop table if exists dbo.S;
go

create table dbo.R(n int, filler char(785)  not null default '');
create table dbo.S(n int, filler char(3990) not null default '');

Filler columns sizes have been rounded down to allow for row overhead. Note that there are no indexes. I have a "numbers" table which I'll use to populate R and S:

insert dbo.R(n) select Number from dbo.Numbers where Number between 1 and 1000;
insert dbo.S(n) select Number from dbo.Numbers where Number between 1 and 50;

We can check just how many pages are involved:

set statistics io on;

select * from R
select * from S

The messages tab of SSMS shows

Table 'R'. Scan count 1, logical reads 100, ...
Table 'S'. Scan count 1, logical reads 25, ...

We have just the right number of pages. A bit of jiggery-pokery will get the behaviour you want to examine

select *
from dbo.R              -- R will be outer
inner loop join dbo.S
    on r.N = s.N
option
(
  force order,          -- dictate which table is outer and which inner
  NO_PERFORMANCE_SPOOL  -- stop the QO from doing something clever but distracting
);

select *
from dbo.S              -- S will be outer
inner loop join dbo.R
    on r.N = s.N
option (force order, NO_PERFORMANCE_SPOOL);

Which gives this in the messages tab (inner table is listed before the outer table)

Table 'S'. Scan count 1, logical reads 25000, ...
Table 'R'. Scan count 1, logical reads 100, ...

Table 'R'. Scan count 1, logical reads 5000, ..
Table 'S'. Scan count 1, logical reads 25, ...

In SQL Server query execution proceeds row-wise. For each row in the outer table the corresponding row(s) in the inner table will be referenced. Since there are no indexes the only option is to read all the rows (i.e. all the pages) from the inner table every time. For R-join-S we have 1,000 outer rows times 25 inner pages giving 25,000 inner page references plus, of course, 100 outer page references. For S-join-R there are 50 rows times 100 pages giving 5,000 inner page references plus 25 outer page references.

In terms of tuple comparisons you are correct - there will be O(R)xO(S) comparisons - 50,000. This is supported by looking at the query plan. For both queries the "Number of Rows Read" for the inner table references are both 50,000.

If there are indexes the query optimizer (QO) has choices other than a table scan. Rewinds may be used for duplicate outer keys. No pages may be read for non-matching keys. In the extreme case where a constraint says there cannot be any matches the inner table may not even be referenced.

The truth is more involved than what you realize. It is true that the outer input of the join will require 1000 logical reads but only if the join key is unique. If it is not, the optimizer can pre sort it and fetch multiple rows at and match all of them at once. As for the inner loop, you are assuming a full scan per iteration. The optimizer will typically prefer nested loops if the inner input is indexed, in which case the number of page fetch will be determined by the cardinality of that set.

My 5 cents - don’t worry about physical implementation details. Invest your resources in perfecting the data model, schema, and code. Let the engine worry about nested loops.

HTH