14,501 questions
The first thing you need to do is install the current CU for SQL 2019 and retest. There have been many changes since the RTM version.
SQL Server 2014 upgrade to 2019 experiencing latency
Frank Garcia
41
Reputation points
Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
SQL Server 2019 Server upgraded from 2014. Issues are high parallelism, application locking up and high latency. Database running in 2014 compatibility mode.
We have tried modifying MAXDOP to 4 with CTFP at 75 to see if this resolves the issue. However, this was not successful though it worked well with other servers. We set maxdop back to 8 aligned with tempdb files and CFTP back to 50. all other configs are at MSFT best practices. I am wondering if it could be related to application logic not compatible with 2019 or perhaps upgrading the version of 2019. Any help would be appreciated.
My best to you all.
SQL Server | Other
4 answers
Sort by: Most helpful
-
-
Frank Garcia 41 Reputation points
2021-06-29T17:51:17.413+00:00 Thank you for your response Tom. I agree.
I was always told by fellow colleagues at MSFT that we should wait for the version after the GDR to upgrade production environments. Since my team was split and a different team is managing the builds, I cannot control this. However, I would like to hear your thoughts on this.
Very much appreciated.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
Tom Phillips 17,771 Reputation points2021-06-29T18:07:17.277+00:00 GDRs are typically for high impacting security or system issues. That is not, and has never been, a valid concept to wait for a GDR before updating any MS product.
You should always test updates before installing them on Production. SQL Server has been very stable for many years. Rarely having issues after updating and when they do, they are resolved in days.
Sign in to comment -
-
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator2021-06-29T19:57:38.543+00:00 Echoing what Tom says. Install CU11 before you do anything else. That's the first step to resolve your issues. Beware that you should run sp_refreshsqlmodule on all scalar user-defined functions after the upgrade.
With the information you have provided, it is impossible to tell what is going on, but please tell us which compatibility level you are at.
-
Frank Garcia 41 Reputation points
2021-06-30T16:48:29.26+00:00 Hi Erland,
Compatibility mode is 2014.
Thank you.
-
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
2021-06-30T17:07:53.703+00:00 If the compatibility level is 2014, this means that can exclude the possibility that the issue due to that any of the optimizer enhancements in recent SQL Server versions have backfired.
I would still recommend that you apply CU11 before any further investigations.
Did you make an in-place upgrade, or did you move to new hardware while you were at it?
-
Frank Garcia 41 Reputation points
2021-07-03T15:16:20.387+00:00 Hi Erland,
New hardware. We applied CU11. Issues seem to have resolved. There has been no more latency.
Thank you all. Enjoy the holiday weekend!
-
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
2021-07-03T18:47:52.427+00:00 Good to hear that CU11 resolved the issue!
Moving to new hardware also means that you change the parameters that the optimizer work with - at least if the available RAM changes.
Sign in to comment -