There are many number of errors documented for SQL Server and are raised when certain error condition occurs which range from syntax errors to problems with instance, databases, jobs, performance, features, etc. When a particular error is received, the error message will have some information regarding the kind of error and what might have caused the error. Sometimes, it is found that the errors are not detailed or misleading and make troubleshooting move in wrong direction or can cause slowdown of resolution the errors.
Out of many errors, some errors are related to performance and the error message may not be enough to understand the reason of the failure. Below is one such error message which occurs due to performance issues with the server performance or with a specific stored procedure or a batch or a individual SQL query. When ever a query hits SQL Server instance, a query plan is created if it is the first time, if not, SQL server optimizer will check for existing plans and reuse if one is already present. There are various reasons the query plan generated may not be optimal and takes more time to complete and sometimes the query can fail altogether with different errors. Below is one such error.
Error: 8623, Severity: 16, State: 1.
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.
This particular error mentioned above is is a rare event and only expected to occur when a very extremely complex queries or queries that reference a very large number of tables or partitions is received by SQL Server. There can be different reasons on what a complex query is referred as, it can happen when attempting to select records through a query with a large number of entries in the “IN” clause like greater than 10,000. There are also some bugs or known issues that can cause this error and some connect bugs are raised too. You can use SQL Server profiler or extended events to track and identify the query causing this error. If this error is recurrent and happening on certain day or time, then we can check if there are any jobs running at that time leading to this error.
The resolution for this error is to simplify and rewrite the complex query. We can also try and divide and get part of the query working and then use extra joins and conditions. We can also use temp table and use temp tables in joins to avoid this error from occurring. We can also try other alternatives like we can try to run the query using the hint option like force order, or option hash join or merge join or using trace flag 4102 with a plan guide. Enabling Traceflag 4102 will revert the behavior of the query optimizer to older version of optimizer for handling semi-joins.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings