An issue with my Execution plan

Question

An issue with my Execution plan

SqlServerPass

SSCommitted

Points: 1709
More actions
March 4, 2013 at 5:27 am

#290152

I have a view.
I made some changes (included few CASEs in SELECT list for a business requirement)
i did a relative cost check between these old and new script with estimated and actual execution plans.
it was 50-50%. so, i conclude that i didnt disturb my query performance.
But, the perfo. went bad.
so, where am i wrong.
Help me pls.
Appreciating your helps always.

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SQL Mad Rafi (3/4/2013)
I have a view.
I made some changes (included few CASEs in SELECT list for a business requirement)
i did a relative cost check between these old and new script with estimated and actual execution plans.
it was 50-50%. so, i conclude that i didnt disturb my query performance.
But, the perfo. went bad.
so, where am i wrong.
Help me pls.
Appreciating your helps always.

Can't tell without seeing what you are seeing. Post the actual exceution plans (.sqlplan file) as attachments, for the old and new scripts.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

As for the costs, they are estimates and are dependent on statistics and heuristics, they can be very far off. I can easily conjure an example with 2 queries in a batch where the one listed at 1% of the cost takes many times longer than the one listed at 99% of the cost.

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 3

Hi Gail. I hope I don't trouble you if you could post this example you mention? I have not dived deeply into the subject of execution plans and am curious about how they can mislead to that magnitude. That is if its not something that takes too long.

thanks

----------------------------------------------------

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

To be honest, I don't have time right now, or for a couple of weeks.

Go hit my blog and look for a post on user-defined functions, IO statistics and the execution plan (can't recall the exact title offhand), or see if you can find a recording of the 24HoP presentation I did Bad plan! Sit!

Short explanation, not everything gets costed correctly and anytnhing that causes cardinality mis-estimates will have the same effect.

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 5

Will do Gail, appreciate your help. Thanks

----------------------------------------------------