August 14, 2019 at 2:08 pm
We have two SQL servers. One production server 2016 sp2 Enterprise edition an d one test server 2016 sp2 Developer Edition.
They are both quite large with the largest database a little over 2Tb and some table with more than 1.4 billion rows.
On both servers we are experiencing the same strange behaviour since some time. Now a lot of queries are beginning to take a lot longer time to execute than before and when examining the execution plans we can se that it doesn't use the "right" indexes any more. When we add index hints to the queries forcing the optimizer to use the indexes we think should be the right ones the performance is back to normal. We are aware of that using index hints is considered bad practice and would really like to avoid it.
So, the question is, why does the optimizer persist on choosing obviously bad execution plans and what can be done to avoid it other than using index hints?
We have updated the statistics of all tables and rebuilt all indexes with only minor improvements. We even updated the statistics with a ten time larger sample then default to see if the low sampling on large tables could be the cause of the behaviour but the problem still remains.
August 15, 2019 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 15, 2019 at 2:25 pm
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".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply