question

MichaelMacGregor-8223 avatar image
0 Votes"
MichaelMacGregor-8223 asked ErlandSommarskog commented

SQL SERVER 2019 CU 12 - Any issues?

We are looking at updating to CU 12 for our SQL Server 2019 installations. Has anyone encountered any major issues with this update? I heard that CU 11 had not fixed the issues with inline scalar functions, is that still the case for CU 12?

Any other information/experience you think might be useful will be appreciated.

Regards,

Michael MacGregor
Database Architect

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.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I have not heard of anything as I can recall.

What issues about scalar functions do you have in mind? I mean that they are not generally broken, but there may specific bugs.

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.

MichaelMacGregor-8223 avatar image
0 Votes"
MichaelMacGregor-8223 answered ErlandSommarskog commented

Hi Erland,

According to Brent Ozar, MS is still struggling with the performance of inline scalar functions and CU 11 scalar function inlining is turned off in the following circumstances:

If the query invoking the UDF uses CTEs.
If the UDF references encrypted columns.
If the UDF contains references to WITH XMLNAMESPACES.

Regards,

Mike

· 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.

As you may know, Microsoft made a very bold change with regards to user-defined scalar functions: they are now being inlined in many cases, and this serves to improve performance in many cases. But since the release of SQL 2019, they have identified a number of situations where inlining results in incorrect results or other undesired behaviour, and the resolution has been in many cases to block inlining in these situations.

I would not really expect very many of them to be unblocked, at least not in SQL 2019, as there probably is major work to do this without undesired side effects.

Of course, if you had a scalar function that was inlined in an earlier build of SQL 2019, and no longer is, you may see a performance regression. Then again, compared to SQL 2017, the performance should be the same.

I know there have also been issues to inlining of scalar functions that have been fixed by other means of unblocking. On the top of my head, I cannot recall any outstanding issue, but what I recall does not say much.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi Mike,

I did not find any MS blog or document that announced the issue is fixed in CU12. I check this in MS blog KB5004524 - Cumulative Update 12 for SQL Server 2019.

Quote from the MS blog KB4538581 - FIX: Scalar UDF Inlining issues in SQL Server 2019.

This cumulative update also blocks inlining in the following scenarios:

• If the UDF references encrypted columns (added in Microsoft SQL Server 2019 CU11)

• If the UDF contains references to 'WITH XMLNAMESPACES' (added in Microsoft SQL Server 2019 CU11)

• If the query invoking the UDF has Common Table Expressions (CTEs) (added in Microsoft SQL Server 2019 CU11)

This blog also did not mention that CU12 fix the issues.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.



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.

MichaelMacGregor-8223 avatar image
0 Votes"
MichaelMacGregor-8223 answered

Hi Cathy,

Thank you.

Though I'm still curious to know if anyone has experienced any other issues with CU 12.

Regards,

Mike

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.