Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execution plan query cost v Logical reads Expand / Collapse
Author
Message
Posted Wednesday, January 19, 2011 10:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:52 PM
Points: 97, Visits: 317
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?
Post #1050185
Posted Wednesday, January 19, 2011 11:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
"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
Post #1050246
Posted Wednesday, January 19, 2011 11:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 7,040, Visits: 12,967
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1050249
Posted Wednesday, January 19, 2011 11:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:52 PM
Points: 97, Visits: 317
Ok thank you for the clarification. Time statistics agree with IO statistics.
Post #1050252
Posted Wednesday, January 19, 2011 11:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
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 2008, MVP
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

Post #1050259
Posted Thursday, January 20, 2011 7:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 15,541, Visits: 27,919
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1050784
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse