This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](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(153.6, 35%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EVS%3C/text%3E%3C/svg%3E)
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.
![](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(35.2, 96%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EUD%3C/text%3E%3C/svg%3E)
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;
I think you missed the OP's question he knows that using legacy cardinality via compatibility change works fine. He is more interested in knowing why this behaviour is specific in 2019. new CE came from SQL Server 2014 and query works fine in 2014-2017 version its only giving problem in 2019 version.
You are right,
Vadim, can you post an execution plan of both (good and bad) query? Your issue might be related to Scalar UDF Inlining?
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
![](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(252.8, 30%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETS%3C/text%3E%3C/svg%3E)
This is a side-by-side upgrade. Brand new servers (Windows 2019, SQL 2019 CU5). Yes, We updated the statistics after the db restore.
This sproc has a try_convert to float and has error handling.
That should not be a problem, I would suggest to post the SP code, if possible. Also CU6 is released would suggest you to apply CU6 and see if this fixes your issue.
In our testing, we took the query from the sproc and ran it with the db in 2019 compatibility mode and the query itself runs quickly. The only difference there would be the absence of the error handling. Thai is why this was mentioned.
The "try_convert" was mentioned because datatype conversions in a query tend to slow down getting the results.
We are planning to install the CU6 in the morning, but nothing from https://support.microsoft.com/en-us/help/4563110/cumulative-update-6-for-sql-server-2019 leads me to believe there is a fix for this issue included.
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.
![](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(192, 23%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJW%3C/text%3E%3C/svg%3E)
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.
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
Yes, Mia. It could be scalar UDF inlining. Or it could be batch-mode on rowstore that goes bad. Or it could be... There are several options, and that is why we have asked for more information.