On: Free e-Book for Index Based SQL Tuning

Or sometimes don't. For sufficiently large joins, a hash join that ignores indexes can save you and a nested loop on the index kill you.