A little more info - this is the same question on MSDN:
[Quote]I have one simple query to optimize but somehow after creating couple of index this query is still having more logical reads and time.
DECLARE @CURRENT smalldatetime
set @current = '6/30/2013'
select KeyInstn, MAX(dateendedstandard) DateEndedStandard
from InternalEstimate with (index(AI_OperatorCBGEstimate_HAP))
where dateendedstandard < @Current and mostrecentsequence = 1 and UpdOperation<2
group by KeyInstn
I have created index as
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[OperateEstimate] ([MostRecentSequence],[DateEndedStandard],[UpdOperation])
INCLUDE ([KeyInstn])[/quote]
Is that two different tables or is one of them a view?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden