SQL Server 6.5: Nested-Loop Joins

If you join two tables, then one table will be selected as outer table
and the other as the inner table. When executing a nested-loop join,
SQL Server will scan the outer table row by row and for each row in
the outer table, it will scan the inner table, looking for matching
rows. To increase the speed of this join operation, you can create an
index on the column that joins the tables. SQL Server can change the
join’s order of the joined tables to increase performance.
There are some situations:

1. There is no index on the column that joins the tables neither
in the outer table, nor in the inner table.

In this case, don’t care how to join these tables, because all tables
will be completely scanned to find the appropriate rows.

2. Only one of the tables has an index on the column that joins the tables.

In this case, SQL Server optimizer will set the table with index as
an inner table, and the other table as an outer table.

3. All tables has an index on the column that joins the tables.

In this case, SQL Server optimizer will set the bigger table (table,
which occupies more data pages) as inner table, and the other table
as outer table.

To see how many data pages the table occupies, you can use
DBCC SHOWCONTIG command.

This is the example:

USE pubs

DECLARE @tbid int

SELECT @tbid = object_id(‘authors’)

DBCC SHOWCONTIG (@tbid)

These are the results from my machine (see Pages Scanned):

[SHOW_CONTIG – SCAN ANALYSIS]

—————————————————————–

Table: ‘authors’ (16003088)  Indid: 1  dbid:5

TABLE level scan performed.

– Pages Scanned…………………………..: 1

– Extent Switches…………………………: 0

– Avg. Pages per Extent……………………: 1.0

– Scan Density [Best Count:Actual Count]…….: 100.00% [0:1]

– Avg. Bytes free per page…………………: 60.0

– Avg. Page density (full)…………………: 97.02%

– Overflow Pages………………………….: 0

– Disconnected Overflow Pages………………: 0

If you need to join more than two tables, the above rules are also
present. The maximum number of tables in one select statement is 16,
so there is no documented way to avoid this restriction in SQL Server 6.5.

The query optimizer of SQL Server 6.5 will produce ranked samples from
n joined tables by 4 and will define amongst them optimum. Outer table
of this sample is considered the most outer in the final order and from
further consideration will be excluded. This procedure will repeat for
n-1 tables, then n-2 and etc. If you will enable the 345 trace flag,
then query optimizer will consider by 6 tables simultaneously, that
brings about increasing a time to compiling, but raises accuracy of
choice of optimum order.