This information is so rare and yet essential when diving to the depths. Without knowing this or having time and skill to uncover the logic that deciphers how the cardinality estimator decides its computation, is like looking for water in a desert. But it’s fishing an abundant river with this in the tackle box. The value of this video is somewhat buried under the massive overarching sense of Darling’s humor but trust me it’s worth it!
I'm surprised that I've never had this problem. I was once on a server that had no maintenance at all. Not indexes, not stats, nothing. It was even on the 2008 compatibility level. When I updated statistics, only about 20 plans changed and there was essentially no difference in performance.
I lost counts how many times even if I've updated stats with full scan on involved tables in a query, SQL optimizer would still do a bad plan, unless a specific option hint is enforced, most common being either a merge/hash/loop join, or legacy or default CE ¯\_(ツ)_/¯
SQL server is a bitch! Plus people write terrible views and procedures that end up in production without oversight. Recently I've been a pain in the ass about making some changes and we are finally seeing a reduction of almost 50% in the overall daily statistics computation time. I'm beginning to think that some of the freed up resources will need to go toward keeping histograms up to date, as trusting the server to do it when needed seems to lead to performance plunges. This seems even more so relevant on tables with many indexes where choosing the incorrect index based on the out of date histogram will really kick you in the nuts. Thanks for the insight.
@@ErikDarlingData yeah, sometimes making the most obvious changes is like having to push something past the communist central committee. I'd like to take the credit, but when it takes half an hour to rewrite a view to make it run 3-10x times faster ... I am just not that good while the initial view is just that bad. But still moving in the direction for the better and getting past some of asinine assumptions as they have been piling up for years.
@@ErikDarlingDatathey are promising me an education budget every year, and the budget seems to be there until you try to draw from it. I will have to tenderize them further, we'll see.
This information is so rare and yet essential when diving to the depths. Without knowing this or having time and skill to uncover the logic that deciphers how the cardinality estimator decides its computation, is like looking for water in a desert. But it’s fishing an abundant river with this in the tackle box. The value of this video is somewhat buried under the massive overarching sense of Darling’s humor but trust me it’s worth it!
Thank you for the poetic comment.
Thanks!!
Thanks 😊
Welcome 😊
I'm surprised that I've never had this problem. I was once on a server that had no maintenance at all. Not indexes, not stats, nothing. It was even on the 2008 compatibility level. When I updated statistics, only about 20 plans changed and there was essentially no difference in performance.
Perhaps auto stats were sufficient? It wouldn't be the strangest thing.
@@ErikDarlingData I thought the same, but a lot of the statistics were 3 years out of date. I have no more guesses.
I lost counts how many times even if I've updated stats with full scan on involved tables in a query, SQL optimizer would still do a bad plan, unless a specific option hint is enforced, most common being either a merge/hash/loop join, or legacy or default CE ¯\_(ツ)_/¯
Ha ha, yep, it's far too common and far too fragile.
SQL server is a bitch! Plus people write terrible views and procedures that end up in production without oversight.
Recently I've been a pain in the ass about making some changes and we are finally seeing a reduction of almost 50% in the overall daily statistics computation time. I'm beginning to think that some of the freed up resources will need to go toward keeping histograms up to date, as trusting the server to do it when needed seems to lead to performance plunges.
This seems even more so relevant on tables with many indexes where choosing the incorrect index based on the out of date histogram will really kick you in the nuts.
Thanks for the insight.
Awesome, glad you enjoyed it! Sounds like you're doing good work over there. Keep it up. It's a tough task for sure sometimes.
@@ErikDarlingData yeah, sometimes making the most obvious changes is like having to push something past the communist central committee.
I'd like to take the credit, but when it takes half an hour to rewrite a view to make it run 3-10x times faster ... I am just not that good while the initial view is just that bad.
But still moving in the direction for the better and getting past some of asinine assumptions as they have been piling up for years.
As a wise man once said - My Rates Are Reasonable©
@@ErikDarlingDatathey are promising me an education budget every year, and the budget seems to be there until you try to draw from it. I will have to tenderize them further, we'll see.
I identify as Vegan - everything I eat has been grass or corn fed 😂
A case like that could end up in front of the supreme court
Cardinality estimator?!? I barely know her! Ok that was pretty bad :/
You get no tomates.
@@ErikDarlingData :'(
@@ErikDarlingData how will i feed my staff of chimp writers?!?