Guidance on Application changes to support the 2014 Cardinality Estimator update

  • Steve

    SSCommitted

    Points: 1695

    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

  • Mr. Brian Gale

    SSC-Insane

    Points: 23174

    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.

  • Ken McKelvey

    SSCoach

    Points: 18329

    https://docs.microsoft.com/en-us/previous-versions/dn673537(v=msdn.10)?redirectedfrom=MSDN

     

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply