4

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.

  • thanks for the quick reply guys. If we leave the db on the previous compatibility level, everything works fine. We're reproducing this behavior on a virtual machine with no load (ie, besides ssms, nothing is connected to it). This is an old db which has been used for more than 10 years... Unfortunately, I'm just migrating it to a new sql server instance... no idea on why it behaves like this... 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... – Luis Abreu Feb 12 at 17:47
  • Btw, 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...if we're not using the last compatibility level, then the memory isn't exhausted (ex: it uses about 10gb when the ram is set to 16)...tomorrow I'll try to gr more info by using activity monitor. – Luis Abreu Feb 12 at 17:52
  • If we bump it up until 140, everything works out as expected... – Luis Abreu Feb 12 at 21:47
9

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.