Query Execution Plan

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Query Execution Plan

Query Execution Plan

Rate Topic

Display Mode

Topic Options

Author

Message

kapil_kk

Posted Saturday, December 29, 2012 5:15 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:16 PM
Points: 1,061, Visits: 1,151

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.

Post #1401160

opc.three

Posted Saturday, December 29, 2012 8:05 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 6,703, Visits: 11,732

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1401168

kapil_kk

Posted Sunday, December 30, 2012 11:13 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:16 PM
Points: 1,061, Visits: 1,151

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

Post #1401291

Koen Verbeeck

Posted Sunday, December 30, 2012 11:56 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 9,370, Visits: 6,467

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.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1401295

kapil_kk

Posted Monday, December 31, 2012 3:12 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:16 PM
Points: 1,061, Visits: 1,151

it takes about 13 min to execute Sad

Post Attachments

query.sqlplan (19 views, 88.21 KB)

Post #1401336

Dave Ballantyne

Posted Monday, December 31, 2012 3:52 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

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
Kent user group

Post #1401350

Evil Kraig F

Posted Monday, December 31, 2012 12:57 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 5,660, Visits: 6,101

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1401486

« Prev Topic | Next Topic »

Permissions