Execution plan query cost v Logical reads

Question

Execution plan query cost v Logical reads

Khades

SSCrazy

Points: 2067
More actions
January 19, 2011 at 10:00 am

#230575

I run 2 queries at the same time with include actual execution plan, and it says query A is taking 3% of the cost, and query B is taking 97% of the cost.
However, when I look at the statistics. Query A has over 15,000 logical reads on 4 tables. Query B only has 839 logical reads on 2 tables.
Which query is performing better here?

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

"Cost" in query plans is just about the most useless piece of junk information ever created. Really. I'm not exaggerating. Most of the time, it's about as useful in performance tuning as a marching band would be.

Have you checked the time stats on them as well as the IO stats?

- 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

LutzM SSC Guru Points: 107049 More actions · Answer 2

One possible cause is using a scalar function in query A.

The query costs in the execution plan tend to be "inaccurate" down to "plain wrong".

You'll get much a better comparison by using either SET STATISTICS TIME or Profiler.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Khades SSCrazy Points: 2067 More actions · Answer 3

Ok thank you for the clarification. Time statistics agree with IO statistics.

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

The costs are estimates. There are a number of things that can make them wrong, out of date stats and associated incorrect row estimations is the big one.

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 5

Multi-statement user defined functions & table variables are estimated based on 1 row, so their "cost" estimates are extremely low when compared to regular & temp tables that have actual statistics & therefore something approaching a "real" cost. Regardless, cost is not a real number and can only act, at best, as a guide. As you've already seen, you have to compare it to other measures, execution time & io.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning