Optimizer using an index that degrades performance!

Question

Optimizer using an index that degrades performance!

Bassist

Newbie

Points: 2
More actions
August 27, 2012 at 9:49 am

#261001

I added an index to a table and according to the Estimated Execution Plan the query (a select statement) is now more resource intensive than without the index - it used to take 67% percent relative to the batch and now it takes 91%. Shouldn’t the Query Optimizer avoid using the index if that is the case? Can I cry now?
Maybe it’s my mistake in relying too much on the "Query Cost (relative to the batch):" percentage that is returned. Is "Query Cost (relative to the batch):" reliable?
Any insight you have to offer would be appreciated!

Viewing 5 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Estimated costs are estimated.

Does it take longer to run (statistics time or profiler duration column)? Does it do more reads (statistics IO or profiler reads column)? Does it use more CPU (Statistics Time or profiler CPU column)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 2

First, the query costs are not a very reliable measure of performance. Second, increased percentage of a reduced cost can be deceptive, in those cases where the costs end up being accurate.

Just in terms of percentages, I find it useful to think in dollars:

If the initial cost was $10, and the percentage of a particular part was 61%, that comes out to $6.10, right?

If the total cost after the index was added went down to $1, then 91% of $1 is $0.91. Which is more expensive, $6.10, or $0.91?

So, even when the percentages and estimated costs are accurate, higher percentage after adding an index isn't necessarily cause for panic.

But also keep in mind that a large number of factors can mess up the estimated costs to the point of them being useless. The most obvious examples are UDFs (other than inline queries), table variables, and parameter values that conflict with expected table stats. IF statements, poorly composed CASE constructs, and CTEs (especially recursive ones) can also destroy the accuracy of costs.

So don't rely on those cost values in execution plans too much. You're much better off with the stats Gail mentioned, like actual CPU time and I/O operations. And those are most reliable when you get them from a trace, not from Set Stats On.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Bassist Newbie Points: 2 More actions · Answer 3

Thank you both very much. That's very helpful information!

I am completely new to performance tuning and see I have a lot to learn. I was hoping that using the "Query Cost (relative to the batch):" percentage would be some clever shortcut. Apparently it's time for me hit the books!

Thanks again.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

I've been told these are good to start with:

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass