October 17, 2011 at 9:52 am
Seems to be the issue of the day that estimated vs actual # of rows don't match.
Care to guess by how much is the estimate off? 😀
Only by 40 K to 1 after the filters :w00t:
October 17, 2011 at 10:37 am
Ninja's_RGR'us (10/17/2011)
Seems to be the issue of the day that estimated vs actual # of rows don't match.Care to guess by how much is the estimate off? 😀
Only by 40 K to 1 after the filters :w00t:
Those estimates look fine to me. Someone who knows much more about SQL Server statistics than I do once told me that they only start to worry if the estimate is off by a factor of 10 or more. Once you account for the fact that the row counts are the sums over the iterations of the nested loops, the estimates are within that limit (per iteration). I'm not saying that query couldn't be improved (it definitely could be better written) but the estimates certainly aren't off by 40,000.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 17, 2011 at 10:45 am
SQL Kiwi (10/17/2011)
Ninja's_RGR'us (10/17/2011)
Seems to be the issue of the day that estimated vs actual # of rows don't match.Care to guess by how much is the estimate off? 😀
Only by 40 K to 1 after the filters :w00t:
Those estimates look fine to me. Someone who knows much more about SQL Server statistics than I do once told me that they only start to worry if the estimate is off by a factor of 10 or more. Once you account for the fact that the row counts are the sums over the iterations of the nested loops, the estimates are within that limit (per iteration). I'm not saying that query couldn't be improved (it definitely could be better written) but the estimates certainly aren't off by 40,000.
Well if you insist on going down that road, the estimated execution count is also off by 3.5X. That alone is OK, but combined with another 3X error for each exec estimate, it adds up real fast.
Then the plan could (or should) be smart enough to know that down the road, 1 and only 1 row can be returned. By that assumption there's more going wrong here.
That being said, I was testing what would happen if I put the correlation operation at the end of the derived tables in the outer apply rather than the inner most query of the apply.
Long story short, better to put in as deep as you can. In this particular case, the query optimizer couldn't really figure out what I wanted.
October 17, 2011 at 10:56 am
Ninja's_RGR'us (10/17/2011)
In this particular case, the query optimizer couldn't really figure out what I wanted.
All right. It's time to complain. Grab your pitchforks, torches, tar and feathers. We're heading back to Seattle to protest. Microsoft promised us software that could read our minds and IT'S NOT HAPPENING. (See Remi's quote for proof).
It's time to get medieval on their a$$e$t$!
Oh, and here's another promise of mind-reading software. I am so holding Brian to this promise: http://pragmaticworks.com/landing/telepathytaskfactoryssis.aspx
October 17, 2011 at 11:01 am
Brandie Tarvin (10/17/2011)
Ninja's_RGR'us (10/17/2011)
In this particular case, the query optimizer couldn't really figure out what I wanted.All right. It's time to complain. Grab your pitchforks, torches, tar and feathers. We're heading back to Seattle to protest. Microsoft promised us software that could read our minds and IT'S NOT HAPPENING. (See Remi's quote for proof).
It's time to get medieval on their a$$e$t$!
Oh, and here's another promise of mind-reading software. I am so holding Brian to this promise: http://pragmaticworks.com/landing/telepathytaskfactoryssis.aspx
I'll let it fly untill sql 2007... when we upgrade :hehe:.
October 17, 2011 at 11:09 am
Ninja's_RGR'us (10/17/2011)
Well if you insist on going down that road, the estimated execution count is also off by 3.5X. That alone is OK, but combined with another 3X error for each exec estimate, it adds up real fast.
Yeah, nah. That's only ~10x off per iteration. Given the separate iterations and sorts, I'm not at all sure what you see adding up 'real fast'. The estimated execution count relates to the input to the top loops join. Given the small number of rows, I might consider materializing that result set in a temporary table. You'll get accurate cardinality and good distribution statistics if you do it right.
Then the plan could (or should) be smart enough to know that down the road, 1 and only 1 row can be returned. By that assumption there's more going wrong here.
Perhaps. There are circumstances where the optimizer recognises the row_number = 1 pattern, but in general when you use non-relational operators like sequence project, you're rapidly moving outside the model, and can expect inaccuracies. Doesn't look to be wildly important in this case.
That being said, I was testing what would happen if I put the correlation operation at the end of the derived tables in the outer apply rather than the inner most query of the apply. Long story short, better to put in as deep as you can. In this particular case, the query optimizer couldn't really figure out what I wanted.
Might be true in your case, probably won't be in others. Long story short, it always depends and it's usually unwise to generalize like that unless you have a very deep knowledge of the product. Part of the art of good query writing is to avoid optimizer anti-patterns. One thing you might like to look at, to eliminate that eager spool: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2008/09/22/distinct-aggregation-considered-harmful.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 17, 2011 at 11:19 am
SQL Kiwi (10/17/2011)
One thing you might like to look at, to eliminate that eager spool: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2008/09/22/distinct-aggregation-considered-harmful.aspx
Just remembered: there's an optimizer improvement in Denali SQL Server 2012 RC0 (not in CTP3) that will unwind single DISTINCT aggregates into a simple stream aggregate for you (eliminating the eager spool and a whole branch of the plan). That will be a great performance boost in cases where it applies, and a lot easier than manually rewriting the query as shown in the link.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 17, 2011 at 11:43 am
Paul,
Have you turned into a zombie and not warned the rest of The Thread? I'm looking at your avatar and wondering if I should pull out my Zombiepocalypse Survival Kit.
@=)
October 17, 2011 at 12:07 pm
SQL Kiwi (10/17/2011)
SQL Kiwi (10/17/2011)
One thing you might like to look at, to eliminate that eager spool: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2008/09/22/distinct-aggregation-considered-harmful.aspxJust remembered: there's an optimizer improvement in
DenaliSQL Server 2012 RC0 (not in CTP3) that will unwind single DISTINCT aggregates into a simple stream aggregate for you (eliminating the eager spool and a whole branch of the plan). That will be a great performance boost in cases where it applies, and a lot easier than manually rewriting the query as shown in the link.
Darn, I was just about to say the same thing.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 17, 2011 at 1:17 pm
Thanks for the link to that Gail, everyone in the office was wondering if I was dying slowly as I'm trying not to laugh aloud in my very quiet office today.
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
October 17, 2011 at 1:29 pm
Evil Kraig F (10/17/2011)
Thanks for the link to that Gail, everyone in the office was wondering if I was dying slowly as I'm trying not to laugh aloud in my very quiet office today.
My predicate is residual.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 17, 2011 at 1:30 pm
Evil Kraig F (10/17/2011)
Thanks for the link to that Gail, everyone in the office was wondering if I was dying slowly as I'm trying not to laugh aloud in my very quiet office today.
Also, was that Grant in a Chuck Norris t-shirt?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 17, 2011 at 1:32 pm
I hate maintenance plans.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 17, 2011 at 1:53 pm
Brandie Tarvin (10/17/2011)
Paul,Have you turned into a zombie and not warned the rest of The Thread? I'm looking at your avatar and wondering if I should pull out my Zombiepocalypse Survival Kit.
@=)
Hah, no it's just face paint (Rugby World Cup) - stand down Zombie red alert.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 17, 2011 at 2:15 pm
SQLRNNR (10/17/2011)
Evil Kraig F (10/17/2011)
Thanks for the link to that Gail, everyone in the office was wondering if I was dying slowly as I'm trying not to laugh aloud in my very quiet office today.Also, was that Grant in a Chuck Norris t-shirt?
Yup. That was the ScaryDBA.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 30,931 through 30,945 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply