logical reads vs. scan count

Question

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?

Answer 1

Here is the killer deal: it January it was costing $12k to buy 864*GB* of RAM. You can get a lot of bang for the buck by simply increasing the RAM of your server up to the point that you will never hit a physical read (after warm up, of course).

Other than that is really hard to give an black or white opinion about either of those data points you present. Sure plan #1 had most physical reads, but are you positive that all tests were done on similarly warmed up cache? Could it be that #1 warmed up the cache for #2, what is your test methodology to ensure all cases are considered on level ground? Even so, if you shell out $500 and double the RAM, would it matter any more? #1 does have the least logical reads...

But then #2 is probably benefit from a high DOP (that one scan can be parallel). Is the wall-clock time of #2 better that #1 after you added sufficient RAM?

How many of these plans run in parallel? Are there tens of queries requesting concurrently a significant memory grant for the hash of #3 and thus creating contention for the RESOURCE_SEMAPHORE? Is the #2 doing a sort and also requesting a memory grant? Will #1 work better since it requires no grant (at least from the info posted...)?

Is really really relative and the question you ask is more like finding one solution for a complex system of equations... there simply could be more that one solutions.

One things is sure: 8M rows should fit in RAM with plenty room to spare. Those physical reads are begging for some memory banks.

Answer 2

For this seemingly very simple query the optimizer will consistently produce the best plan according to its cost model. The cost model is fairly accurate. So my recommendation would be to leave the choice to SQL Server.

Second recommendation: Measure query duration for all three variants with a hot cache. Then decide. (Don't decide based on reads and scans and such. What matters for you is duration.)

In general, to choose the best join type (or indexes) requires understanding of how the join algorithms work. That's too much information to post here.