Has SQL Server Performance Improved?

  • It's been my expectation that SQL Server will run slower because of all the stuff they're doing to figure out if a plan should be reverted and a bunch of other things.

    As the old science adage warns, "If you measure it, you change it".

    To be honest, I'm scared to death of our impending migration from SQL Server 2016 to SQL Server 2022 because MS has tried to make things more "Gumby Proof" and that requires measuring the size of "Gumby's feet" much more often than a simple shoe change would require.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ok... I just did some extensive testing using Brent's code both as is and with some mods to give the test table a bit more bulk.

    Using a MAXDOP of 1, like Brent did, the 2019 Compatibility Level ran using about 38% LESS CPU and Duration than 2016.  (In other words, 2019 was FASTER)

    Using a MAXDOP of 4, like my normal setup, the 2019 Compatibility Level ran using about 46% LESS CPU and Duration than 2016. (In other words, 2019 was FASTER)

    My thought was, "What am I doing differently because both of those sets of tests indicate that 2019 isn't a problem with performance?"

    ...  And then I reread the Brent's article and finally saw the "parenthetical fine print"...

    (In this demo case, 2019 compat level actually works beautifully, dropping the CPU time down by about 1/3, and I wish the client’s case was that easy. They already tried that before they called me. Bummer.)

    Maybe I'm missing something obvious but... IMHO, that strongly suggests that the real problem is actually "just" a yet-to-be-discovered problem in the actual client implementation (including but not limited to Windows or 3rd party software installed) or hardware or ??? and NOT an inherent problem with 2019.

    I also did the same testing in the 2022 (160) Compatibility Level with similar results.

    The good part of all this is that, much like the little girl on the "6th Sense" said after her gut wrenching expulsion of stomach fluids, "I feel much better now"  about the impending migration from 2016 to 2022 that we're going to make in the next few months.

    From Steve's Article:

    "...you might document some queries (in addition to Brent's) and record the results. That might help you decide when you upgrade."

    Well said... That's one of the many reasons why "Baselining" is so very important (and not just for SQL Server version upgrades).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I tried duplicating Brent's results (using his exact code, unchanged) on two different machines that each had named instances of SQL Server 2016 SP3 and SQL Server 2019 CU19. My results (comparing the two versions) were basically identical, certainly not outside the margin of error.

    If I changed the database compatibility level to 150 for the SQL Server 2019 instance, the MAXDOP 1 query results were MUCH faster (around 43% faster) on SQL Server 2019 compared to SQL Server 2016. This seems to echo Jeff's findings.

    SQL Server 2019 has so many manageability improvements compared to SQL Server 2016 that make it much easier to work with as you do performance tuning on a daily basis. Pretty much all of the customers that I work with are on SQL Server 2016 or SQL Server 2019, so I deal with this every day.

    I hate to see people get frightened about upgrading to a newer version of SQL Server by something like this.

     

     

  • Welcome aboard, Glenn!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Glad to see some additional testing. While I do think most people will run at the latest compat levels, I also know far too many people get hung up at previous levels, especially with upgrades. I haven't had the chance to test, mostly because I have to reinstall SQL 2016, which I just haven't made time to do.

    I think the latest versions of the query processor are better, though for a specific query it's possible there will be issues.

  • By its very nature, some of the interleaved execution that we're getting with all the intelligent query processing is going to cause some queries to slow down as the execution pauses. Also, as Jeff says, measurements and calculations on those measurements add overhead.

    HOWEVER

    Based on tests I've run, and no, not enormous client machines, overall, yeah, performance in 2019 (and 2022) has improved. Everywhere? No. All the time? No. Regardless of circumstances? Of course not. And if you're running at the old compatibility levels, then you're only getting a very small subset of the increases in performance, so it's actually not that surprising that you can see worse performance on the newer version, than on the older version. After all, you're taking away improvements.

    This feels like an edge case. A large one, and a troubling one, but not the norm.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 7 posts - 1 through 6 (of 6 total)

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