Recently I was helping a client with Comprehensive Database Performance Health Check and we found a very strange error when we tried to run a few of their stored procedure. Let us learn how we can fix Error 8632 – Internal Error: An expression Services Limit Has Been Reached.
Error 8632: Internal Error An expression Services
After the SQL Server upgrade, my client had issues with the query performance regression where few queries and stored procedures were not performing well. During the investigation, we found out that there were few queries that were giving the following error.
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
One of the first reactions was to restart the SQL Server but due to the nature of the business and continuity, it was not possible to restart the SQL Server at the client’s place. Additionally, I do not see a point to restart the SQL Server unless there are major configuration changes or patch upgrades.
For investigation when we made a copy of the stored procedure, each copy of stored procedures worked perfectly fine. This helped me understand that the error is not related to anything but the cached stored procedures.
The solution to this error was very simple. We opened each of the stored procedures and recreated them with the same name and everything started to work fine. In some scenarios, our stored procedure had another stored procedure inside it and we had to recreate or recompile all the stored procedures which were nested to make the error go away.
Here is the command which you can run to recompile the stored procedure.
EXEC sp_recompile N'SPName'; GO
Do let me know if you have faced any such error and if yes, how did you resolved this error as this will help other blog readers who are facing the same error. You can also connect with me on Twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)
What version of SQL was throwing this error?
We are getting this error for queries in windows application. We don’t use stored procedures. What do you suggest?
I found these work arounds:
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;
Less desirable workaround:
Use SQL 2017 compatibility mode
I got this error in sqlserver 2019. My database migrated from sql 2017. I have got 4 databases and these databases access to another via views etc. 3 databases compatibility level were 2012 and main database compatibility level was 2019 (I don’t known why. its’ mistake). I changed main database compatibility level from 2019 to 2017 then errors gone. After that I changed all databases compatibility level to 2019 (150) and errors gone. May be issue related with this.
Tank you , Tank You very very much