Statistics, Data Distribution, and Cardinality - SQL Server Query Performance Tuning - page 201

Figure 12-9. Automatic statistics for table Test1
To verify how a different result set size from the two tables influences the decision of the query optimizer, modify
the filter criteria of the query to access an opposite result set size from the two tables (small from Test1 and large from
Test2 ). Instead of filtering on Test1.Test1_C2 = 2 , change it to filter on 1:
SELECT t1.Test1_C2,
t2.Test2_C2
FROM dbo.Test1 AS t1
JOIN dbo.Test2 AS t2
ON t1.Test1_C2 = t2.Test2_C2
WHERE t1.Test1_C2 = 1;
Figure 12-10 shows the resultant execution plan, and Figure 12-11 shows the Extended Events session output of
this query.
Figure 12-10. Execution plan for a different result set
Figure 12-11. Trace output for a different result set