EasyBoy (4/16/2014)
ChrisM@Work (4/16/2014)
EasyBoy (4/15/2014)
I did try almost all combination of altering columns for index, but somehow logical reads are same (67000).Did you look at the execution plans to see if the hash aggregate was replaced with a stream aggregate? If you're not sure, then create the index which Gail suggests and post the execution plan here.
Please find the updated execution plan.
Can anyone reading this thread account for the columns in the defined values list or output list of the index seek operator on this plan? Here they are:
[InternalUseOnly_New].[dbo].[OperatorCBGEstimate].KeyInstn,
[InternalUseOnly_New].[dbo].[OperatorCBGEstimate].DateEndedStandard,
Uniq1002,
[InternalUseOnly_New].[dbo].[OperatorCBGEstimate].BlockGroupFIPS,
[InternalUseOnly_New].[dbo].[OperatorCBGEstimate].UpdOperation,
[InternalUseOnly_New].[dbo].[OperatorCBGEstimate].DateEstimated
Only the first two columns are required by the next operator (Stream Aggregate), and three of the remaining four aren't even referenced by the query. What little literature I have doesn't come close and I can't find anything useful with Boogle either.
Thanks.
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