First of all, there are a couple of good articles (I don't have the links handy just now) that demonstrate that a 25% sample rate takes about as long as a full scan for statistics rebuilds. I've not confirmed that but the people that wrote the articles and code to demonstrate that are knowledgeable people. That being said, if I rebuild a stat, I rebuild it using a FULL SCAN.
The other thing might just be that your code isn't scalable or wasn't "optimized" and it finally hit the tipping point where it makes a bad execution plan decision based on estimated cost.
Last but not least, the optimizer isn't perfect. Contrary to popular belief, the optimizer doesn't always come up with the "BEST" plan. Many times, it comes up with what it thinks is a "good enough" plan simply because it would take way too long to estimate all of the permutations to come up with a "BEST" plan. A lot of people poo-poo the idea of using index hints (but then praise forced execution plans... go figure) but this is a perfect use of them. If the optimizer isn't quite getting it right, then helping it do better with index hints (directives, really) is not the massive code-smell that many insist it is.
As always, "It Depends".
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)