Which query plan is better?

Question

SQL

There is a query that selects approximately 100k rows.
He had such an execution plan
habrastorage.org/files/ac3/889/550/ac38895507ef4d0...
It shows 2 table scans, this is a condition like:
LEFT JOIN table ON table.somefield = @param
I added an index on this field somefield, and the plan became like this:
habrastorage.org /files/0a1/2be/863/0a12be86308c45d...
The execution time hasn't really changed. Is it possible to say which of these plans is obviously worse?

Answer 1

It's hard to say without knowing the details, however... No, I don't like any of them!
Try hard hints to turn off the brains of the server. Sometimes option (loop join, force order) helps better than creating indexes. Well, in join, kick table with (index (TMPIND_0)) or whatever it is.

Answer 2

You would though show request, and it is better with the data.
table scan and hash match indicate that something else can be done,
the question is whether it is necessary?
Works very slowly?
Is the index clustered? Covering your join?

Answer 3

use set statistics io, time onto understand what really slows down