Why is a table Scan being performed?

Question

SELECT X.ID, X.Field4
FROM 
        #TaxInvoiceData T
INNER JOIN
        xxx X
        ON  T.Id = X.Id
        AND Field2 = @VAR     
        AND Field3 = 'S'

When I run a query a Full table scan on table X. I do not understand why because the Primary Key of Table X is

ID INT ASC
Field3 CHAR(2) ASC
Field2 DATETIME ASC  Unique Non-clustered

There is also an index on

Field2 DATETIME ASC  Non-Unique Non-clustered

Doing just

SELECT ID
FROM xxx
WHERE 
    Field2 = @VAR   
AND Field3 = 'S'

Does an Index Seek

Thanks in advance.

Answer 1

Short answer: because the optimizer thinks it would be faster.

However, let's try to read the optimizer's mind.

Since you haven't provided full table schema, I'm going to assume that there's a clustered index on xxx.ID and that #TaxInvoiceData is a heap. You're expecting a plan where the PK index is probed for every row in #TaxInvoiceData, but you're selecting xxx.Field4 which is going to require a bookmark lookup for every match. This could result in 29,000 random I/O requests. Ouch.

Conversely, SQL Server could (and apparently is going to) just perform a larger amount of more efficient sequential I/O doing the table scan and is probably doing a speedy hash match against #TaxInvoiceData.

So what can you do? You could create a covering index including Field4. Or you could use index and join hints to force the plan you're looking for (but I suspect performance wouldn't be as good as you hope). Is this query used frequently enough that it is giving your application performance problems or are you just looking to eliminate table scans on principle? If the latter, you may find the overhead of getting rid of the scan isn't worth it in the end.

---

Edit:

Since you've mentioned that there's no clustered index on the table, this also may affect how efficient lookups from the index are. Unless this table is seeing extremely heavy insert activity, consider changing your PK to clustered. That alone may change the plan, and even if it doesn't it's likely to speed up other operations due to reduced overhead.