Any chance you can send a non-anonymized plan, and table structure? Are both distincts necessary (e.g. there will really be duplicates of those narrow column sets)?
The real question, IMO, is not HOW to turn an index scan into an index seek. The real question is whether you want to. And at this point I am pretty sure the answer is no.
It's hrd to draw many conclusion from what you posted because (a) it is anonymized which hides a lot of helpful information; and (b) it is an estimated plan only so we cannot see if there are any critical mis-estimates. But I do see that the estimated number of rows is equal to the table cardinality, which matches the absence of a WHERE clause anywhere in the query,
In other words: your query directs SQL Server to read all rows in the entire table. An index scan is far more efficient for that than an index seek.
Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.
Answers and Comments
query plan x670
plan explorer upload x480
asked: Jan 16 at 06:11 PM
Seen: 59 times
Last Updated: Jan 17 at 09:25 AM
Usage of three index for the same table
Wrong index scan
Help with removing the sort form the plan
possible to get MAX() with only an index scan?
what indexes to apply/drop
Difficulty finding covering index for this
this query is taking too long, any ideas how to improve performance with indexes
indexing problem after implicit conversion in sql server 2005
SQL is ignoring my index hint and using a clustered index.
SQL Server execution plan is using PK instead of index
© 2013 SQL Sentry, Inc. All rights reserved.