Sql-server – Changing to compatibility level 150 makes SQL Server consume all available memory

compatibility-levelfunctionsmemorysql serversql-server-2019

We're moving an existing DB to a new SQL Server 2019 instance.

If we change the compatibility level to 150, nothing works. Task manager shows that sql server service is consuming all available memory and all queries time out with an error that says

There is insufficient system memory in resource pool internal to run this query

The RAM is completely consumed until no query can be run (all end with the previous error I've mentioned). We've tried with 16, 32 and 64GB of RAM.

Can anyone give any tips on how to debug this?

Notice that it's not a query issue…just changing the compat level is enough to make SQL start exhausting the memory.

Even running a simple scalar function (which internally uses several Substring and cast calls – no table or view selects) is enough to make it consume all the available memory.

Best Answer

You're encountering a known bug with scalar UDF inlining. You can disable inlining using one of the methods here (or by using a lower compat level, as you've discovered yourself):

Disabling Scalar UDF Inlining without changing the compatibility level

Or install CU2 for the permanent fix.

...even running a simple scalar function (which internally uses several Substring and cast calls - no table or view selects) is enough to make it consume all the available memory...

Reading this part of your issue reminded me of this really interesting blog post from sqL_handLe:

SQL Server 2019 Scalar UDF inlining - OOM in some cases

He demonstrates a scalar UDF that doesn't do any data access, but uses up all the memory on the box (even one with almost a TB of RAM):

Not just crashing itself. Crashing any other query on the instance that was trying to allocate memory (eg stealing against a query memory grant).

This should be fixed in SQL Server 2019 CU2, it looks like this is the KB for it:

FIX: Scalar UDF Inlining issues in SQL Server 2019

This cumulative update includes several fixes across the following areas for scenarios where a query that uses Scalar UDF Inlining may return an error or unexpected results:

  • ...
  • Out of memory conditions and memory leaks due to very large scalar UDFs
  • ...

If you're unable to install CU2 for some reason, you can use the newer compatibility level as long as you disable scalar UDF inling at the database level like this:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Or at the query level, as described in the docs article I linked to earlier.

Related Question