Post reply

Performance Issue After SQL 2019 Upgrade

  • February 24, 2020 at 2:35 am

    #3728044

    Hi,

    I am experiencing heaps of timeout issue on my SQL Server 2019 database that were not happening on the previous version I have.  I read that 2019 is supposed to be faster than the old version but I don't see the benefit at the moment.  Server settings are as follows:

    Previous

    - Windows 2012 (64 BIT)

    - Processor Intel(R) Xeon(R) CPU E5-2620 v4 @ 2.10ghz (4 Cores, 4 Logical Processors)

    - RAM 32 GB

    - Microsoft SQL Server Standard (64-bit) version 11.0.2100.60

     

    Current

    - Windows Server 2019 Standard version 1809

    - Processor : Common KVM processor 2.93 ghz (2 Sockets 4 virtual processor)

    - RAM : 32 GB

    - Microsoft SQL Server Standard (64-bit) version 15.0.2000.5

     

    What I did as a summary of migration are as follows:

    - Restore 2012 db version to SQL 2019 Server

    - Set the database compatibility level to 150

    - Set RAM allocation to SQL Server to 24Gb and the remaining 8Gb to OS, etc

    Note that the database has heaps of functions which should also benefit from SQL Server 2019 due to scalar function inlining but still,  I am experiencing heaps of timeout which are not happening in the previous version.  I am thinking maybe I miss one or two critical SQL Server settings that must be enabled on 2019 that is causing performance issue?

    Thanks.

    • This topic was modified 4 years, 7 months ago by  xedarenas.

  • February 24, 2020 at 3:43 am

    #3728048

    Did you try using the old cardinality estimator?

    Check out this article by Pinal Dave.

  • February 24, 2020 at 1:21 pm

    #3728149

    Ah, you've hopped the great Cardinality Estimation Engine gap represented by 2014. Sounds like your system is one of those that has problems with the new CE. Prior to 2014, the CE was exactly the same from SQL Server 7 to SQL Server 2012. In 2014 they updated the CE. Anyone moving a system from pre-2014 to post-2014 potentially is going to hit a problem. Most queries didn't notice the change. Some queries got faster. A few queries (overall, world-wide, not talking about your system) suffered severely.

    Here's what I'd do to fix this. First, set your database to the old CE using ALTER DATABASE SCOPED CONFIGURATION to set the LEGACY_CARDINALITY_ESTIMATOR to on. This will put you back to the old CE, the easiest way possible. You could try changing the compatibility mode back, but you lose more than just the new CE. Your call on step one.

    Next, turn on Query Store. Run your load for a while (you'll have to define what "a while" looks like on your system) in order to gather standard behavior. Then, switch back to the new CE. You should see the queries that are suffering a regression show up in the Query Store reports.

    Then, use Plan Forcing to push the old functionality on the problem queries until you can get around to rewriting them.

    And yes, the core of this problem is that the code is probably not optimized and the old CE was a little more forgiving of bad code than the new CE is. So plan on spending time addressing the code, but use the methods I outlined above to buy yourself time to get that done.

    ----------------------------------------------------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

  • February 24, 2020 at 1:55 pm

    #3728178

    +1,000,000 on that post, Grant.

    --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

  • February 24, 2020 at 2:59 pm

    #3728196

    Please upgrade to the latest CU ! You are missing essentials and RTM already had a mandatory hotfix at launch time !

     

    https://support.microsoft.com/en-us/help/4536075/cumulative-update-2-for-sql-server-2019

     

    Please let me know if that helped !

    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

  • February 24, 2020 at 8:59 pm

    #3728392

    Johan, upgrade to latest CU is on my radar.  I will try this and will update this thread if it helps.

    pietlinden, Grant Fritchey,  I did try to revert to old compatibility mode and it did not help although I haven't try to set LEGACY_CARDINALITY_ESTIMATOR on while using the current compatibility mode which is 150 (is this okay btw?).  If both will not help, I will just tackle the problem head on.  I already install Brent's sp_blitzcache and I already have the list of SQL with the most cpu and memory resource hugger.

    I thought I maybe missing some obvious configuration that is why I am having this problem or if the new hardware especially the RAM does not suffice.

    • This reply was modified 4 years, 7 months ago by  xedarenas.

  • February 25, 2020 at 12:24 pm

    #3728547

    Also verify the host-configuration as the processor is virtualized (Common KVM) it must run on some host. Is power-saving disabled ? how are the resources distributed between VM's? Are the VM-drivers installend (if needed)? What is the virtualization technology (Hyper-V would still say Xeon). NUMA-configuration ...

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

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