TomThomson (4/17/2014)

---

EasyBoy (4/16/2014)

---

Thanks Scott and Lynn for your inputs.

As suggested by Scott i have made changes in existing index with filtered index (suggested by Lynn). And i am able to bring the logical reads down to 30k from 70k.

CREATE NONCLUSTERED INDEX [AI_OperatorCBGEstimate_14947]

ON OperatorCBGEstimate ( DateEndedStandard, UpdOperation )

INCLUDE ( KeyInstn, MostRecentSequence )

WHERE MostRecentSequence = 1 and UpdOperation < 2 WITH ( DROP_EXISTING = ON )

I may be confused here, but if I've understood correctly the optimiser might be able to do somewhat better with an index like this:-

CREATE NONCLUSTERED INDEX [AI_OperatorCBGEstimate_14947]

ON OperatorCBGEstimate ( KeyInstn, DateEndedStandard )

WHERE MostRecentSequence = 1 and UpdOperation < 2 WITH ( DROP_EXISTING = ON )

It won't get the logical reads down any more than the one you tried, but it mght reduce other costs.