August 13, 2020 at 2:04 pm
Hello all.
We recently had an opportunity to discover a hidden treasure in SQL server as we began rolling out 2016 in production. This primarily entailed changing the compatibility mode from 110 to 130. In doing this, our lower volume databases showed no significant change with the metrics we were monitoring. However, when we made the update on an instance with similar traffic but much larger volumes we saw a dramatic departure in the CPU standard behavior. It went through the roof. (or to the roof considering it can only be 100%)
We have since been able to identify increased deadlock issues that happen in 130 compatibility vs 110 compatibility. I had originally considered that the cardinality estimator was the culprit. I am assuming that this change made a fundamental shift on how my execution plans were calculated.
My question is this. Has anyone had similar experiences moving from 110 to 130? If so, what techniques did you use to identify newly offending code and what resources did you employ to track down the abhorrent behavior?
Thanks in advance for sharing your stories.
Steve
August 13, 2020 at 3:55 pm
I personally have not run into this - my upgrade from pre-2014 to post-2014 went 100% smooth for the ones that did migrate and the ones that we haven't migrated yet, we need to do more testing before we pull that trigger.
But I think that your friend here is going to be query store. I believe the recommended thing to do is upgrade to 2014+, turn on query store, capture enough data that most (if not all) stored procedures and ad-hoc queries have been run, then switch compatabiltiy mode and if things slow down, have a peek in query store to see which execution plans changed for the worse and address those queries.
Another thing you can do is run the data migration assistant prior to upgrade which will tell you which things are going to be breaking changes and behavior changes.
If you have any database monitoring tools, those can help too. Several companies make good ones; it just depends on your needs and budget.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 14, 2020 at 2:19 pm
https://docs.microsoft.com/en-us/previous-versions/dn673537(v=msdn.10)?redirectedfrom=MSDN
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply