Performance problem

  • Hi

    I have one server running SQL server 2017 and one running SQL server 2019. The developer has reported that the SQL 2019 is much slower than the SQL 2017.

    Both servers are virtual and has the same spec regarding memory, CPU, OS ( Windows 2019).  The settings in SQL is the same  on the ones that the app require

    I haven´t received any examples of question but they say that the difference is significant( minutes instead of sec )

    The database from the 2017 server  is restored from backup on the 2019 server. Same compatibility level (130)

    What is the big difference between the two versions that can cause this problem ?

    /Jonas

  • Nothing springs to mind; it's not like, for example, when the cardinality estimator was changed between 2012 and 2014. Though you could, perhaps, have the old cardinality estimator enabled in one and not the other, so you could check that as performance can be significantly different between the two.

    Otherwise you really you need details of the queries that they say are performing significantly different; then you can start looking at things like the query plans, checking the definitions are the same between environments, parameter sniffing, etc.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • What's the version of your SQL2019 ? ( latest CU ? )

    Did you try setting the db level to 150 ?

    Another option is to test the database configuration setting "LEGACY_CARDINALITY_ESTIMATION"?

     

    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 but most of the time this is me

  • Are the statistics up to date?

  • I've seen no types of standard degradation between 2017 and 2019. So, I'd suggest examining all the server settings, cost threshold, the things already suggested here.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • (1) Verify that the 'cost threshold for parallelism' has been set properly (i.e. increased from the default) on the new instance

    (2) Verify that the tempdb settings -- number of files, etc. -- are optimal for the new instance

    (3) Verify that IFI is enabled for the new instance

     

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Hi

    Thanks for the replies !!

    I saw that the latest CU ( CU16) where not installed( should have been installed by WSUS, have to look into why not) . After I applied it the application worked much better and the developers are happy again. 🙂 It turned out to be a bug in SQL that hit the application

    " Applies to Documentum Server 20.4

    summary

    When SQL Server 2019 is used for the Database of Documentum Server, it takes longer than Documentum Server using other DBs to install DAR and create a large number of objects at once.

    Cause

    This is a Bug specific to SQL Server 2019. "

    Regards

    Jonas

  • Jonas Jisendal wrote:

    Hi

    Thanks for the replies !!

    I saw that the latest CU ( CU16) where not installed( should have been installed by WSUS) . After I applied it the application worked much better and the developers are happy again. 🙂

    Regards

    Jonas

    Out of interest, what was it running prior to you updating it to CU16?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • A little embarrassed to say.... - none , only security updates..

    Should have been applied automatic by WSUS. Works on the other servers I got.  Some investigation to do there....

    /Jonas

  • Thanks for the feedback on what it turned out to be, Jonas.

    --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)
    Intro to Tally Tables and Functions

  • How fix SQL server performance issues?

    Start by checking your wait stats

    1-Ensure your TempDB database is configured optimally.

    2-Make sure you're running index maintenance frequently.

    3-Implement indexes that provide a benefit to your queries.

    4-Check your most expensive queries and stored procedures.

    5-Monitor your performance counters.

  • jefferyjordan26 wrote:

    How fix SQL performance issues? Start by checking your wait stats

    1-Ensure your TempDB database is configured optimally.

    2-Make sure you're running index maintenance frequently.

    3-Implement indexes that provide a benefit to your queries.

    4-Check your most expensive queries and stored procedures.

    5-Monitor your performance counters.

    Item 2 is mostly incorrect for OLTP because a single row lookup takes exactly the same path on a 99% logically fragmented index as in does on one with 0% logical fragmentation.  As for the rest, "It Depends".  I will tell you that if your index maintenance includes the use of REORGANIZE, there's a very good chance that you're causing more problem than you're curing especially on indexes that you rely on lowing the fill factor to supposedly delay or prevent fragmentation.

    The biggest bang for your buck is to keep index statistics up to date, especially if you have "ever-increasing" keys in your indexes.

    I suppose you're going to tell folks how to accomplish the other 4 points, right?

    --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)
    Intro to Tally Tables and Functions

  • This was removed by the editor as SPAM

  • jefferyjordan26 wrote:

    How fix SQL performance issues? Start by checking your wait stats

    2-Make sure you're running index maintenance frequently.

    Everything Jeff said.

    But, one exception, columnstore indexes. Reorganization and rebuild do things for columnstore beyond fragmentation. So consider using them because of that.

    For those interested, it's the internals of a columnstore index. The delta store, where changes are stored, is a non-compressed, non-pivoted, b-tree index. As you exceed 100,000 rows in the delta store, you'll get that moved to compressed & pivoted, but there's all sorts of caveats around that because it's not a part of a straight data load. Again, details, details, details, in this. However, reorganize actually moves stuff out of the delta store. It helps. Rebuild, does the whole 9 yards with the data (at a much higher cost, of course). So, for columnstore, I do recommend index maintenance.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • Grant Fritchey wrote:

    jefferyjordan26 wrote:

    How fix SQL performance issues? Start by checking your wait stats

    2-Make sure you're running index maintenance frequently.

    Everything Jeff said.

    But, one exception, columnstore indexes. Reorganization and rebuild do things for columnstore beyond fragmentation. So consider using them because of that.

    For those interested, it's the internals of a columnstore index. The delta store, where changes are stored, is a non-compressed, non-pivoted, b-tree index. As you exceed 100,000 rows in the delta store, you'll get that moved to compressed & pivoted, but there's all sorts of caveats around that because it's not a part of a straight data load. Again, details, details, details, in this. However, reorganize actually moves stuff out of the delta store. It helps. Rebuild, does the whole 9 yards with the data (at a much higher cost, of course). So, for columnstore, I do recommend index maintenance.

    Aye!  Thanks for the correction on the ColumnStore indexes concerning index maintenance.  I always forget about that and agree that Reorganize on those is not the same monster that it can be for RowStore indexes.

    --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)
    Intro to Tally Tables and Functions

Viewing 15 posts - 1 through 15 (of 20 total)

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