Query optimization help

Question

Query optimization help

Viewing 5 posts - 46 through 49 (of 49 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Why did you create two identical indexes?

How many environments do you have which will run this query?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

EasyBoy SSCertifiable Points: 5301 More actions · Answer 2

First i made changes to existing index but as due to business rule, i have rollbacked the changed and created new index similar to 14147.

ScottPletcher SSC Guru Points: 100949 More actions · Answer 3

So how on earth are you supposed to properly tune a table when you can't change its indexes?? :w00t:

Edit: Moved the emoticon after the ??.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I have downloaded and looked at both of the latest sqlplans, both use the same index due to the use of the WITH(INDEX(AI_OperatorCBGEstimate_HAP)) index hint on the query.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Lynn Pettis (4/23/2014)
I have downloaded and looked at both of the latest sqlplans, both use the same index due to the use of the WITH(INDEX(AI_OperatorCBGEstimate_HAP)) index hint on the query.

File HAP.sqlplan

Here's the query from the plan:

select KeyInstn,MAX(dateendedstandard) DateEndedStandard

from ObjectViews..InternalOperCBGEstimate WITH ( INDEX ( AI_OperatorCBGEstimate_HAP ) )

where dateendedstandard < @Current and mostrecentsequence = 1 and UpdOperation<2

group by KeyInstn

The index seek uses filtered nonclustered index [AI_OperatorCBGEstimate_HAP] with an estimated i/o cost of 12.6.

The seek is on DateEndedStandard and there's no residual predicate.

Forced index is false.

File 14197.sqlplan