Error 701 Insufficient memory in SQL Server 2019 when running Scalar Functions in Sage 500 2021

Came across a weird issue today where the function fnIMRoundQty in Sage 500 2021 was causing a 701 Insufficient memory error on SQL 2019.  However, on a SQL 2016 system, the issue would not occur.  Doing a little research, I found that there is a known bug in SQL 2019 where scalar functions can result in a memory leak.  This is supposed to be fixed in a future update, but the work around is to turn off the Scalar UDF In-lining feature.  The article that discusses this is https://social.msdn.microsoft.com/Forums/en-US/b1db7482-57da-44fc-84ee-cebe131dd882/sql-server-2019-memory-leak?forum=transactsql which references https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15#disabling-scalar-udf-inlining-without-changing-the-compatibility-level

Basically, the statement you will run is:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

One way to know that in-lining is on is to do a SQL trace while making a call to a scalar function.  You will notice in the trace that it does not display any statements from within the function.  Once you turn off In-lining, a trace will now display the statements within the function.

I hope this small article helps.

  • I've run into this before as well. It will not occur if you are on SQL 2019 but database compatibility is set to a lower SQL level, which also resolves some of the other code performance problems if you aren't setting or using legacy cardinality. The problem is actually sourced with fnIMRoundQtyWrk and that function's use of else statements that the query optimizer seems to have some difficulty with in the parsing operation.