I'm joining a small table (1,000 rows) against a large table (8M rows) in SQL Server 2008. The join uses a nonclustered covering index on the large table, and the join can produce three possible query plans. I'm trying to figure out which plan is better, but also I want to generalize this knowledge so next time I can better know what heuristics to use when looking at SQL I/O statistics.
Plan #1 is a loop join and emits statistics for the large table like this:
Scan count 2582, logical reads 35686, physical reads 1041, read-ahead reads 23052
Plan #2 is a merge join and emits statistics like this:
Scan count 1, logical reads 59034, physical reads 49, read-ahead reads 59004
Plan #3 is a hash join and emits statistics like this:
Scan count 3, logical reads 59011, physical reads 5, read-ahead reads 59010
The covering index is ordered by
(ID, Date). The query returns data for about 50% of the IDs and, for each ID, returns a contiguous chunk of the most recent 3 months of data, which is usually about 1/4 or the rows for each ID. The query returns about 1/8 of the total rows in the index. In other words, the query is sparse but consistently so.
My assumption is that plan #1 is awful for this workload, because moving the disk head around 2,500 times (or even 1,041 times) is far more expensive than a sequential disk scan. I also assume that #3 and #2 have similar, sequential (and therefore more efficient) I/O patterns.
But is there a case where plan #1 is really best, where "best" means less impact on the I/O subsystem and less impact on other queries running concurrently?
Or does it really depend on many variables like the kind of disk subsystem I have, index fragmentation, etc. If "it depends" are there any rules of thumb to approach the problem?