Query slow on SQL 2019

Vadim Sudenko 1 Reputation point
2020-09-01T09:37:21.583+00:00

There is one specific query that has been executed on MS SQL 2014 for long time. The query is running on AG secondary RO replica. The query execution time on MSSQL 2014 is about 5 seconds. After migration to MSSQL 2019 the same query is running about 5 minutes.
We found by trying out the different compatibility modes we can use SQL 2014, 2016, and 2017 modes and get fast results. Only in SQL 2019 does it slow down. Also we found that running the query alone runs quickly in any mode.

There is only problem with SQL 2019.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,809 questions
0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Uri Dimant 206 Reputation points
    2020-09-01T10:52:54.37+00:00

    Hi Run in scope your db and see if there is an improvment

    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON ;
    ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = On;

  2. Shashank Singh 6,246 Reputation points
    2020-09-01T11:27:45.537+00:00

    How did you migrated to SQL Server 2019 inplace upgrade or side by side ? . Did you made sure statistics were updated on secondary RO replica ? Did you made sure SQL Server 2019 is patched to latest CU. Please refer to this KB Article to see if you are hitting a specific bug

  3. Erland Sommarskog 101.8K Reputation points MVP
    2020-09-01T21:57:30.763+00:00

    With every release, Microsoft makes improvements to the optimizer, but given what kind of game optimization is, there are always situations where the improvements will backfire.

    As Shanky says, we need to see the query. We also need to see the actual query plans, both for the lower compat levels (where it is running fast) and one for compat level 150 (where it is slow). I think you will need to rename the .sqlplan files to .xml before you attach them here.

    0 comments
  4. Jeffrey Williams 1,891 Reputation points
    2020-09-01T21:58:27.26+00:00

    Do you happen to have any scalar functions in the code? This could be an issue with inline scalar functions introduced in SQL Server 2019 - but without the code it is impossible to determine.

    Reviewing the execution plans should help determine what is different between environments.

    0 comments
  5. m 4,271 Reputation points
    2020-09-02T02:20:24.447+00:00

    Hi @Vadim Sudenko ,

    That might be caused by the Scalar UDF Inlining.

    Try to use the latest CU or disable the Scalar UDF Inlining to have a test.

    Similar case: the-query-in-sql-server-2019-is-very-slow

    If the reply is helped,please do “Accept Answer”.
    BR,
    Mia