SQL Server : Index Seek with very high Scan Count
I believe this has already been answered and would be helpful for you if you can refer to below discussion:Also, what action where clause is performing needs to be looked into:

Why so many logical reads?

Since Logical Reads are from the data cache (memory) I would think the fact that there is a lot of reads will make little difference and it would seem that the second query is more efficient when reading a lot of data in small chunks while the first query reads the data in large chunks.

Overall, the query performance significantly can be improved using below several aspects as well:


Only the correct subset of rows from the * table to be retrieved, potentially reducing physical IO and locking contention
The CPU intensive hash functions to be eliminated
The memory footprint for both data and hash buckets to be reduced.


Index Seek will be used only if conditions from WHERE or ON clauses are the first in the list of columns of the Index. Also, this fields must be selective which means that the condition must filter just a little percent or rows from a table.

Also, you don't need to include column that is part of the clustered index into list of columns of a nonclustered index, because Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.

Actually, this is very complex subject. To know more about using indexes in Sql Server you can read this article https://technet.microsoft.com/en-us/library/jj835095.aspx