Force SQL to use a named index....

Should SQL chose a less than optimal plan, you can influence index choice.I say influence, i mean FORCE :)

A SQL Query (I know it uses * !)

SELECT Table1.*, Table2.*
FROM Table1
LEFT JOIN Table2 ON Table1.Id = Table2.Id

Add an index hint e.g.

SELECT Table1.*, Table2.*
FROM Table1 WITH(INDEX(IndexName))
LEFT JOIN Table2 ON Table1.Id = Table2.Id

You can still add NOLOCK to that hint too...

SELECT Table1.*, Table2.*
FROM Table1 WITH(INDEX(IndexName),NOLOCK)
LEFT JOIN Table2 ON Table1.Id = Table2.Id

A hint could equally have been applied to the joined table or both.

Links :
Brent Ozar has some good reasons for NOT using index hints though so it's only for use as a last resort.
Andrew Chen's post on Index Hints
You can also force SQL's choice of Query Plan