January 26, 2011 at 11:30 am
Till now my assumption was cost inside an execution plan would mean the query taking most time to execute? Is that correct? I have a weird situation where the cost is most by execution time is least.
January 26, 2011 at 11:40 am
The "cost" is technically an assumption of how much effort will go into that part of the query. Effort includes CPU time, RAM, I/O.
Really, "cost" is a misleading number that is only useful some of the time, and useless or harmful the rest of the time. Largely, I ignore it.
- 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
January 26, 2011 at 11:54 am
what would be the best way to find slowest query in a batch. One taking most time
January 26, 2011 at 12:39 pm
You can't base performance measurement off of execution plans. The only way to absolutely know which statement in a batch took longer is to have measurements, using extended events or trace events to capture each statement.
However, cost can be, sometimes, a rough indicator of which part of a query may have run the longest. Please note that sentence is chock full of weasel words. In short, you can use the cost as a measure, but if you think it really has a relation to reality, you could be in for a rude awakening.
"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
January 26, 2011 at 12:45 pm
If you want to work it out yourself (definitely a skill worth having if you're a DBA), you can get data on batches and their resources and run-time by running traces.
If you want a tool that will do the work for you, try Confio Ignite. I've found it very reliable for performance tuning query pieces.
- 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
January 26, 2011 at 1:04 pm
The cost is an estimate. There are many things that can make it wrong, incorrect row count estimates being the most common
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
January 26, 2011 at 2:02 pm
thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply