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:-
[cpde="sql"]CREATE NONCLUSTERED INDEX [AI_OperatorCBGEstimate_14947]
ON OperatorCBGEstimate ( KeyInstn, DateEndedStandard )
WHERE MostRecentSequence = 1 and UpdOperation < 2 WITH ( DROP_EXISTING = ON )[/code]
It won't get the logical reads down any more than the one you tried, but it mght reduce other costs.
Tom