Query Execution Plan

Question

Query Execution Plan

kapil_kk

SSC-Insane

Points: 21316
More actions
December 29, 2012 at 5:15 am

#287113

Hi,
I have to improve the performance of some stored procedure as they take long time to execute.
I checked the cost in Execution plan but some shows 100% n some other than that...so can anyone plz tell me what should be the cost of a execution plan so that I can know in which SP I need to make change.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

The operator costs within an execution plan will always add up to 100%. If you are running multiple stored procedures in a batch then the relative batch cost of all stored procedures in the batch will add up to 100%. Make sense?

If you have a specific question about the actual execution plan attach the .sqlplan file to this thread so we can have a look.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

can we improve the %of table scan elements in Query execution plan?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

kapil_kk (12/30/2012)
can we improve the %of table scan elements in Query execution plan?

Maybe if you posted the actual execution plan, people can actually try to help you.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

it takes about 13 min to execute 🙁

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

Hi there,

Firstly there is quite a big descrepancy between estimate and actual row counts throughout the plan this (normally) points to out of date statistics.

use "UPDATE STATISTICS" to ensure that they are up to date and re-execute the query.

Please post that plan if performance has not improved

Dave

Clear Sky SQL
My Blog[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Hm, there's a few nasty pieces to this but the biggest problem is the fctTxPayPlan table.

Estimate: 39,000 rows. Actual: 23 billion.

Might problem there. The table's a heap, get an index on it. It's predicating on dimLocationID and ID, use those as the leading edge of your clustered index for fctTxPayPlan and you'll get a ton of improvement. Updating statistics here won't help, as you don't have an index in play there and I doubt you've manually created columns statistics. Even a nonclustered index on those two fields and including any columns you need from that table would significantly improve this query.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA