none
Strange Error 8632 "expression services limit has been reached" after move to SQL Server 2019 RRS feed

  • Pergunta

  • I moved database to SQL Server 2019 and now I'm getting this error while executing one very old stored proc:

    Msg 8632, Level 17, State 2, ...
    Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

    However, as soon as I set database compatibility option to SQL Server 2017:

    ALTER DATABASE ... SET COMPATIBILITY_LEVEL = 140

    the same procedure in the same database runs successfully.

    The error reappears after I run this:

    ALTER DATABASE ... SET COMPATIBILITY_LEVEL = 150

    Please advise.

    Thank you,

    Eugene


    • Editado Eugene123456 domingo, 2 de fevereiro de 2020 19:22
    domingo, 2 de fevereiro de 2020 19:21

Respostas

  • Thanks, that is what I need to know. I just wanted it confirm that were scalar functions involved.

    There are a couple of options. One is to change the functions to have WITH INLINE = OFF, to prevent them from being inlined.

    You can also modify the query by adding this hint:

    OPTION (USE HINT ('DISABLE_TSQL_SCALAR_UDF_INLINING'))

    There is also a database-scope configuration with the same name, to turn off the feature for the entire database.

    I would think that the query hint is the best option.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marcado como Resposta Eugene123456 segunda-feira, 3 de fevereiro de 2020 22:35
    segunda-feira, 3 de fevereiro de 2020 12:45

Todas as Respostas

  • Hi,

    Is changing this procedure an option? Do you know what is the exact query being run in this SP? 

    I'm guessing something may have been changed internally in SQL Server 2019 and this procedure now causes this problem. Most likely there was some issue with that procedure before but it was not apparent in older versions. If you can investigate the actual query being run and if possible simplify it, it may solve this issue.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    domingo, 2 de fevereiro de 2020 19:55
    Moderador
  • My guess is that this procedure calls a scalar user-defined function of some complexity.

    A major improvement in SQL 2019 is that scalar user-defined functions now can be inlined. This can lead to serious improvement of performance. But as with all performance improvements, there are situations where it can backfire, and this seems to be such a case.

    There are several ways to deal with this beside fiddling with the compatibility level, but I would like to know more before I give any advice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    domingo, 2 de fevereiro de 2020 21:48
  • The procedure has seven common table expressions, two of those call the same scalar returning user-defined function. The procedure CROSS JOINs those CTEs with other SELECTs to produce flat single-row resultset.

    If there is any advice you can provide given such scarce info, then please do. The procedure is indeed complex; describing it here would be cumbersome and pointless without describing the underlying data it deals with.

    Thank you,

    Eugene

    domingo, 2 de fevereiro de 2020 23:47
  • Hi Eugene,

    Refer to Microsoft Support, this issue occurs because SQL Server limits the number of identifiers and constants that can be contained in a single expression of a query. If the number after the expansion exceeds the limit, the query cannot run.

    The most direct way is to rewrite the query.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    segunda-feira, 3 de fevereiro de 2020 12:06
  • Thanks, that is what I need to know. I just wanted it confirm that were scalar functions involved.

    There are a couple of options. One is to change the functions to have WITH INLINE = OFF, to prevent them from being inlined.

    You can also modify the query by adding this hint:

    OPTION (USE HINT ('DISABLE_TSQL_SCALAR_UDF_INLINING'))

    There is also a database-scope configuration with the same name, to turn off the feature for the entire database.

    I would think that the query hint is the best option.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marcado como Resposta Eugene123456 segunda-feira, 3 de fevereiro de 2020 22:35
    segunda-feira, 3 de fevereiro de 2020 12:45
  • This fixed it:

    ALTER FUNCTION ...
    RETURNS int
    WITH INLINE = OFF
    AS ...

    Thank you!!

    Eugene

    segunda-feira, 3 de fevereiro de 2020 22:35