question

FrankGarcia-3349 avatar image
0 Votes"
FrankGarcia-3349 asked ErlandSommarskog commented

SQL Server 2014 upgrade to 2019 experiencing latency

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

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.

https://support.microsoft.com/en-us/topic/kb4518398-sql-server-2019-build-versions-782ed548-1cd8-b5c3-a566-8b4f9e20293a

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

FrankGarcia-3349 avatar image
0 Votes"
FrankGarcia-3349 answered TomPhillips-1744 edited

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

See:
https://techcommunity.microsoft.com/t5/sql-server/announcing-the-modern-servicing-model-for-sql-server/ba-p/385594

1 Vote 1 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

FrankGarcia-3349 avatar image
0 Votes"
FrankGarcia-3349 answered ErlandSommarskog commented

Hi Erland,

Compatibility mode is 2014.

Thank you.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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?

0 Votes 0 ·

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!

0 Votes 0 ·

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.

0 Votes 0 ·