I came across a scenario where a query is picking totally unrelated index in the execution plan. Here is the case:
FROM table1 A WITH(nolock)
JOIN table2 B WITH(NOLOCK) On (A.tranid=B.TranId and A.ProdType=B.Prodtype )
WHERE A.transittime Between '2017-11-14 23:30:00.000' AND '2018-01-13 23:30:00.000'
There are couple of indexes which are best suited for this query like :
CREATE INDEX IDX1 ON table1(tranid,ProdType,transittime)
CREATE INDEX IDX2 ON table2(tranid,ProdType )
But surprisingly, the query gets below index scan in plan:
CREATE INDEX IDX3 ON table1(Visitduration )
this index column Visitduration is no where part of my query, but still optimizer scans it. Why index IDX1 seek in not happening here? What scenario might cause it? the query return around 200K records.
If I use index hint to force the IDX1 use, then my performance gets slower compared to IDX3 use.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.