What do you do when something that has been working for months suddenly stops working and gives you a SQL Error 8623? Worse yet, what do you do when that error message tells you to either rewrite your extremely complex query or spend some cash on a Microsoft customer support services call?
In my case, this happened with a critical application that was not only costing the company money every minute it was not working but could also affect the health and well-being of people that the application ultimately served. My hope is that, if you encounter a similar situation with this error, this blog will help to relieve some stress and get things working much more quickly.
SQL error 8623 is a severity 16 error and, as such, should be able to be corrected by the user. The message this error returns directs you to contact customer support services for more information. The entire error message as shown below is:
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
Since my application had been running for some time without issue, I reviewed my logs and monitoring tools. I found my server was still appropriately sized and was running well below capacity in both CPU and memory. Further, my average read and write response times were sub 2ms. Resources were not an issue on this server.
Given that my application had been running and performing well for some time, I immediately thought that my stats must have gone stale which triggered a new plan to be generated or, at least, attempted to be generated. Given the amount of data and number of databases on the server, I did not want to blindly run index and stats maintenance as it would have taken longer than what would be acceptable. So I rebuilt all the indexes on the tables included in the query to ensure that my stats would be as good as possible and that my indexes were not fragmented. I even flushed the plan cache; however, this did not resolve my problem.
A quick sidebar: I was fortunate and knew where the application was failing exactly what procedure was causing the issue. If you do not know what query or procedure is throwing this error, there are many good blogs that explain how to set up an extended event to capture that information. I did not test the code from this link but it does show how to set this up to capture the query that is causing the error: https://dba.stackexchange.com/questions/204960/sql-server-error-8623-severity-16-state-1
During some preliminary searching on the web, I found this error would be returned in older versions of SQL server when an “IN” clause contained too many items. When looking at my query, while it was very complex, it did not contain any “IN” clauses. Additionally, given the nature of the application, any change that could affect the result of the query would have to go through a rigorous testing regimen. Rewriting this query was out of the question.
What I did find in the query is that there were dozens of complex derived tables and CTEs being used that referenced derived tables and CTEs. I started putting it together that the optimizer was likely getting overwhelmed keeping track of all of these in-memory objects and could not create the execution plan.
Since this was a super sensitive mission-critical application, I decided to bypass the optimizer. SQL server will allow for query hints to force different things in the execution plan like index usage, or maxdop, as well as join order. When running the query in management studio, I can see that the error happens on line 203.
Line 203 is the beginning of a very complex query, so at the end of this complex query (line 914), I added the query hint OPTION (FORCE ORDER). I did not want to blindly add this add hint to every query in the procedure as this will tell SQL to run this query as I wrote it, which may not be the most efficient way but will, in essence, bypass the optimizer and create an execution plan based on what was written. Since this was not changing the queries, I was able to release this without further testing and it allowed the application to function again.
It is very possible that adding RAM would have allowed this procedure to run through the optimizer but, since the monitoring tools did not show signs of memory pressure, I was not able to go down this road. While investigating how and when an execution plan is generated, I began to believe that the density of my data had grown to the point of overwhelming the optimizer, even though in all but the most extreme cases, SQL will be able to manage complex queries even when there is little RAM in the system.
The force order query hint is described well at http://www.sqlserver.info/syntax/force-order-query-hint/
We are now working to simplify the query and remove the query hint. I know that, over years, code can turn into a ball of yarn that can become ugly and complicated. In this case, this query hint allowed us to avoid an emergency while we work to simplify the code. Query hints, while helpful and powerful, should be used with caution. Running query hints long-term or even migrating to newer versions of SQL while using query hints can sometimes cause more harm than good and should only be used when absolutely necessary and removed as soon as possible. Evaluating why a hint is needed and resolving the root cause is the better solution.
In any case, the next time you get a SQL Error 8623, remember that – in most cases – the optimizer will give you the best results.