Compatibility Level Confidence

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721524

    Comments posted to this topic are about the item Compatibility Level Confidence

  • David.Poole

    SSC Guru

    Points: 75402

    I feel the compatibility level facility is a technical fix for a human problem.

    In the ideal world we would be able to install the latest SQL Server edition an have all our databases tested at the latest level.

    In the real world our new development will be done at the latest level,  those apps where an upgrade provided a solution to an existing problem will be ported.  The other applications will be ported in their own good time as business priorities allow.   The human problem is that  fixing unbroken apps is always going to be at the bottom of the list of priorities.

    I feel that compatibility levels are OK as a safety rope to allow you to upgrade your databases but, to use a climbing analogy, I like to have 3 points of contact with the rock face.  I never quite trust that rope.

  • Jon 0x7f800000

    Old Hand

    Points: 362

    Is it viable to check for application performance regressions by running a workload on SQL Server 2019, recording metrics, and then running the same workload on the same app & DB with it's compatibility level set to a "known good" version such as 2016? (and then comparing metrics and query plans)

  • Johan Bijnens

    SSC Guru

    Points: 134310

    For what it's worth: Even last year, I've seen vendors still installing SQL2005 Express with their "state of the art" stuff.

    I've always been "the first with who made a fuss out of it".

    I see many industry grade technical companies just sticking with old versions because their stuff works on it and they do not want to invest in newer versions because they feel their software doesn't take any advantage of it.

    Strange thing is, that attitude is less when they use oracle db.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] but most of the time this is me

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721524

    David.Poole wrote:

    I feel the compatibility level facility is a technical fix for a human problem.

    In the ideal world we would be able to install the latest SQL Server edition an have all our databases tested at the latest level.

    Does this mean we need complete automated testing? Is that the issue?

    I think there is something here, but with MS releasing on that 24-36mo cycle, that is a lot to ask across any minimal number of applications and databases. Plus there's patching involved. That brings me back to the testing issue, which is likely more necessary than ever since we have security issues and we know that those need to be dealt with, as unpatched systems are the main way that exploits from criminals occur, after social engineering.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721524

    Jon 0x7f800000 wrote:

    Is it viable to check for application performance regressions by running a workload on SQL Server 2019, recording metrics, and then running the same workload on the same app & DB with it's compatibility level set to a "known good" version such as 2016? (and then comparing metrics and query plans)

    First, it likely is, but how would you do that? I don't know of a good tool to allow this.

    In terms of an upgrade, this is more that you are on 2016, but then you upgrade to 2019 and need to run as 2016 compat. In that case, I think this would be helpful to determine the upgrade (or consolidation) is fine.

  • Jon 0x7f800000

    Old Hand

    Points: 362

    Steve Jones - SSC Editor wrote:

    Jon 0x7f800000 wrote:

    Is it viable to check for application performance regressions by running a workload on SQL Server 2019, recording metrics, and then running the same workload on the same app & DB with it's compatibility level set to a "known good" version such as 2016? (and then comparing metrics and query plans)

    First, it likely is, but how would you do that? I don't know of a good tool to allow this.

    In terms of an upgrade, this is more that you are on 2016, but then you upgrade to 2019 and need to run as 2016 compat. In that case, I think this would be helpful to determine the upgrade (or consolidation) is fine.

    Thanks, Steve. This would be performance testing a fresh instance of the application (so it's purely a test instance). We have automated performance testing tools, so the idea is to run those, flip compatibility levels, and then run them again. The goal is to support 2019, so we would do separate correctness testing to make sure everything is still working fine.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721524

    Can I ask what tools you're using? I assume you're getting timing from third party tools. What I'd love is a way to check execution plan regressions.

  • Jon 0x7f800000

    Old Hand

    Points: 362

    Steve Jones - SSC Editor wrote:

    Can I ask what tools you're using? I assume you're getting timing from third party tools. What I'd love is a way to check execution plan regressions.

    Sorry, I may have mistakenly implied that we're checking the execution plans automatically - unfortunately that is a manual process (an automated tool would be great though!). We know which queries are the most complex, so if we see a dip in any of the application functions, we'll have a good idea of where to look. The metrics collection and the workload are automated at least, so it makes the process not-too-painful.

  • francesco.mantovani

    SSC Eights!

    Points: 833

    I have several servers on SQL Server 2019 but all the Compatibility Levels are on 110.

    The sysadmin has been doing these "upgrades" since SQL Server 2012 I suppose.

    And now we are in 2020 and I have to upgrade all the databases.

    The cherry on the cake? All application hitting the servers are written in Access and the are basically legacy code from the '90. We have 3 developers which are 60, 66 and 74 years old.

    I'm using 3 tools:

    1. Microsoft Data Migration Assistant to make an audit about the compatibility
    2. Microsoft Query Tuning Assistant which comes with SSMS and can gives you insights about the query and the Compatibility Level.
    3. I will use Agent Ransack to scan the code and find T-SQL words that are deprecated.

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

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