question

VadimSudenko-4241 avatar image
0 Votes"
VadimSudenko-4241 asked ·

Query slow on SQL 2019

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-general
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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

6 Answers

UriDimant avatar image
0 Votes"
UriDimant answered ·

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 ·
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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

Shashank-Singh avatar image Shashank-Singh ·

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.

0 Votes 0 ·
UriDimant avatar image UriDimant Shashank-Singh ·

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?

0 Votes 0 ·
Shashank-Singh avatar image
0 Votes"
Shashank-Singh answered ·

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


· 4 ·
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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

TimShirey-5871 avatar image TimShirey-5871 ·

This is a side-by-side upgrade. Brand new servers (Windows 2019, SQL 2019 CU5). Yes, We updated the statistics after the db restore.

0 Votes 0 ·
TimShirey-5871 avatar image TimShirey-5871 TimShirey-5871 ·

This sproc has a try_convert to float and has error handling.

0 Votes 0 ·
Shashank-Singh avatar image Shashank-Singh TimShirey-5871 ·

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.

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered ·

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.

·
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered ·

Hi @VadimSudenko-4241,

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


· 1 ·
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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

ErlandSommarskog avatar image ErlandSommarskog ·

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.

0 Votes 0 ·
MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered ·

Hi @VadimSudenko-4241,

Is the reply helpful?
Do you solve the issue?

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

·
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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