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)