Lynn Pettis (4/18/2014)
TomThomson (4/17/2014)
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.
Sort of like the one I posted earlier except I only indexes on DateEndedStandard and made KeyInstn an included column.. Based on the query if I were to index on both columns I would swap them around from what you have listed.
Yes, in fact I started from the one you posted. I think making the item being grouped on they first key in the index may reduce the hashing cost - with the index elements in that order the optimiser knows that it will get consecutively biggish bunches of rows with the same KeyInstn, and may be capable of deciding not to compute the hash in the case where it's going to be the same as the previous one. Although I don't know enough about sql server's optimiser to say whether it will or won't do that, I do know that some optimisers decades ago did so it seems possible that SQL Server does. I was cLutching at straws here, of course, it is definitely a try it and see thing not a firm recommendation.
Tom