July 25, 2011 at 8:40 am
It is true in the case of “a horse as a point in the absolute vacuum”. In the real world optimizer sometimes chooses very suboptimal plan, due to timeouts or a simple fact that it is not perfect.
And yes, data change, but statistically and from a choosing the optimal plan prospective they can still be pretty much the same. For example, I don’t expect data in our user table will change dramatically from the statistical prospective: we already have 30 million users there. One should always know his data very well and anticipate the way the data will change.
I have yet to see that SQL Server will produce a query plan that more than 5% faster/has less IO than the one I've crafted manually, but I’ve seen countless times the queries that run hundreds times slower and causing timeouts, not speaking about poor user experience because of the optimizer choosing bad plan. And we do update statistics regularly.
So in theory they are bad and optimizer is just perfect, but the real life is not so black and white.
August 2, 2011 at 12:47 pm
Of course it's not black-and-white. Nothing in databases ever is.
- 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
August 2, 2011 at 1:05 pm
So when do you plan to release your better version of the optimizer?
No offense but the ms engineer working on this are, how can I say this, NOT MORONS.
I too don't always understand their choices but given that they've done about 1 trillion more tests than me I choose to let them do their job and use the best they can send my way.
August 2, 2011 at 1:23 pm
Ninja's_RGR'us (8/2/2011)
So when do you plan to release your better version of the optimizer?No offense but the ms engineer working on this are, how can I say this, NOT MORONS.
I too don't always understand their choices but given that they've done about 1 trillion more tests than me I choose to let them do their job and use the best they can send my way.
Nobody writes a perfect tool to automatically generate query plans here. Or are you implying that there is no human being more intelligent than MS optimizer written by "NOT MORONS" and passed a lot of tests?
Well, it didn't pass all mine... Let me also remind that optimizer hints included to the product by the same people for a reason.
It is certainly your call to trust the tool 100%
August 2, 2011 at 1:30 pm
Thanks, but I'm not available (and won't be for years).
And no I don't trust it blindly, but I also never had the need to use hints of force the optimizer to see things my way.
I can help it to his thing better to close the missing gap.
I meant no attack if that's how you perceived it.
August 2, 2011 at 1:39 pm
---
August 2, 2011 at 1:39 pm
Sorry for my impulsive reply. I just finished handled a performance issue caused by "it knows better" attitude. No attack perceived or meant
August 2, 2011 at 1:45 pm
stolbovoy (8/2/2011)
Sorry for my impulsive reply. I just finished handled a performance issue caused by "it knows better" attitude. No attack perceived or meant
...and you couldn't handle the MS might know better (or good enough).
Interesting :-D.
Happy venting, however you do it ;-).
August 2, 2011 at 1:58 pm
Ninja's_RGR'us (8/2/2011)
Thanks, but I'm not available (and won't be for years).And no I don't trust it blindly, but I also never had the need to use hints of force the optimizer to see things my way.
I can help it to his thing better to close the missing gap.
I meant no attack if that's how you perceived it.
In 10 years of performance tuning, I've forced an execution plan once. So can't say "never", but can say "very, very rarely". Same for join hints - one time. Again, same for index hints.
On the other hand, I've used all kinds of tools other than forcing plans to get SQL Server to do what I want, instead of the default behavior, and all of that comes under this heading.
Things like snapshot isolation on certain queries, breaking some procedures down into temp tables instead of just derived tables or subqueries, quirky updates, even a few cursors to break down locking scope, and table variables to move data in a way that avoids MS-DTC. All of those are comparable to forcing an execution plan, in terms of bypassing MS expertise and using my own.
And all of them have issues with changing data patterns being a potential future problem. It's about the tradeoffs.
But I do have to say, forcing an execution plan is one I try like crazy to avoid. Makes future refactoring too difficult.
- 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
August 3, 2011 at 4:58 am
Actually forcing a plan will screw you MUCH more often based simply on the sproc input parameters rather than based on data skew or changes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 3, 2011 at 8:23 am
Optimizer hints is a very powerful tool and it is extremely easy to shoot yourself in a foot with. One should intimately know the data and SS before attempting to use them. They are not the first thing to try, but last.
I just been opposed to the "definitely bad thing" and "never-ever" attitude.
August 3, 2011 at 8:27 am
stolbovoy (8/3/2011)
Optimizer hints is a very powerful tool and it is extremely easy to shoot yourself in a foot with. One should intimately know the data and SS before attempting to use them. They are not the first thing to try, but last.I just been opposed to the "definitely bad thing" and "never-ever" attitude.
I'm with you here.
But between never-ever and maybe 3 times over 40 years experience, there's not a lot of room to play with ;-).
August 4, 2011 at 8:34 am
I'm totally with stolbovoy on this one. It depends on your environment, for sure, but we force LOTS of optimiser hints here. We run huge databases (terabytes), multi-on-line-users (15,000 transactions per second), and believe me - we have to encourage the optimiser to get things right lots of the time. Stored procedure parameter sniffing is the biggest reason, and we simply cannot afford to let the optimiser choose a potentially bad plan if it happens to have to recompile a proc with some atypical parameters.
Before I worked here, I'd have agreed with everyone else - only use optimiser hints very rarely and very cautiously. The culture here is completely different though, and it works! In fact, if we didn't do it, we'd never be able to handle the transaction rate that we do.
Horses for courses, as the saying goes.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply