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: 101105 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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