September 18, 2019 at 5:10 pm
Hello,
This past summer we migrated to OS 2019 and SQL 2017. I have the automatic tuning feature enabled for the DB in question and all has been fantastic until this morning. Each morning index maintenance (Ola Hallengren) runs with rebuilding statistics option. Once load began, (~ 5,000 users) massive blocking began (LCK_M_X) slowing down the entire system. I found the problem procedure and when looking at the regressed queries, it showed up in the #1 spot. There were 3 other plans in cache, but their plans were even worse.
I'm guessing that is why the automatic tuning feature didn't select another plan, but what about the tuner itself recompiling the plan? I did this manually and sure enough, all the blocking went away and SQL began purring again.
Is there a setting I am missing?
September 19, 2019 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 20, 2019 at 3:09 pm
I think you will have to manually tune that query
maybe there are a few missing indexes and your data size has got to a point where it can't find a better plan
I've had this a few times and found that because data size had changed in different percentages between 2 tables, sql then wanted a new index
get an execution plan , post it on here and we might be able to give you a starting point
MVDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply