May 29, 2008 at 8:35 pm
hi
I need to tune a stored procedure. for that iam seeing the execution plan after the whole procedure is executed. is that correct way or I need to run individual queries n see the execution plans for them.
Thannks
May 29, 2008 at 10:00 pm
The query plan for the whole stored proc should include the plan for each statement in the sproc. This appears as nodes within the tree structure which does tend to male life a little difficult since you need to work out which parts of the tree match each statement (and the tree might be showing the statements that actually executed which makes it harder and then there are loops....). So for large sprocs, it can be very challenging to understand the query plan.
It may be easier to break up the sproc and just tune bits of it.
May 30, 2008 at 6:23 am
Yes, that's normal behavior. As noted above, if there are multiple statements within a stored procedure, each statement will have an individual plan. Each plan will show as a percentage of the whole execution (in graphical execution plans). Red Gate is offering a deal on a free book that explains a lot more about execution plans. I posted the beginnings of an article on how to break down complex execution[/url] plans that might be worth a read.
"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
May 30, 2008 at 2:06 pm
garyreeds (5/29/2008)
hiI need to tune a stored procedure. for that iam seeing the execution plan after the whole procedure is executed. is that correct way or I need to run individual queries n see the execution plans for them.
Thannks
What you're doing is correct.
- 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
May 30, 2008 at 6:32 pm
I'd say to never look at the percentage of batch in the execution plan... it lies... a lot...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 2, 2008 at 5:20 am
Really, never?
I find the percentage of the batch to be a pretty useful guide most of the time. Not all of the time, no, but more often than not.
"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
June 2, 2008 at 7:35 am
I often find the percentage useful, at least as a starting point.
- 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
June 2, 2008 at 8:47 am
The main problem with those percentages come up fairly frequent when UDFs are used.... be really careful. But I do agree that it is a good "starting" point.
* Noel
June 2, 2008 at 11:34 am
Grant Fritchey (6/2/2008)
Really, never?I find the percentage of the batch to be a pretty useful guide most of the time. Not all of the time, no, but more often than not.
I've found that it lies more often than not. So, just like using a cursor, it's a practice that I've come to avoid. I don't even use it as a starting point... I've seen it where the better, more performant code is listed as taking 98% of the batch while the really slow code was listed as on 2% of the batch. And it's not always with UDF's and other RBAR.
I'll see if I can dig up some examples...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 2, 2008 at 11:37 am
Whoa!
I've definately seen variation, but nothing that extreme. Yeah, I'd love to see those examples. I'm just starting an article on how to break down complex queries. I may have to start rewriting.
"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
June 3, 2008 at 5:44 am
I've seen cases where the batch % is way off, but IME it hasn't been the norm. Maybe I'm just working with simpler queries
Generally I've found that odd joins (cross or triangular), UDFs (table or scalar), remote data (OpenQuery, OpenXML), table variables and bad statistics can all cause cost estimates to be way off.
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
June 3, 2008 at 5:58 am
Amen to table variables and bad statistics. Especially table variables. I'd still love to see what Jeff's got on the subject.
"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
June 3, 2008 at 6:42 am
One example he gave to me a while back on this subject concerned TOP and ROWCOUNT.
UDFs are my pet peeve in execution plans at the moment. I wish there was a way to see, from the graphical plan, what the scalar UDFs are doing.
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
June 3, 2008 at 8:25 am
There was this time in my early DBA days that I swore by the the % on the exec plans ... I then modified a very complex set of queries to use UDFs based on the "tests" ... Lets just say that on development the server could not handle the "horrible" performance coming from the "improved" queries :D.
From that day on... I test, test and ... you guessed it "test" .
* Noel
June 3, 2008 at 9:20 am
Jeff Moden (6/2/2008)
Grant Fritchey (6/2/2008)
Really, never?I find the percentage of the batch to be a pretty useful guide most of the time. Not all of the time, no, but more often than not.
I've found that it lies more often than not. So, just like using a cursor, it's a practice that I've come to avoid. I don't even use it as a starting point... I've seen it where the better, more performant code is listed as taking 98% of the batch while the really slow code was listed as on 2% of the batch. And it's not always with UDF's and other RBAR.
I'll see if I can dig up some examples...
Yeah, I've seen plenty of that. But I've also seen plenty of places where it gave me a clue as to where to start looking. Many times it's useless, but many times it is useful. At least to me. It's mainly a matter, to me, of knowing what makes it give wrong data, and being able to predict that behavior.
- 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
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply