Recently after migrating to SQL Server 2016 one of our app start to failing.
While debugging I found the query which was failing.
The query is like this.
This was working fine SQL Server 2014 but now failing in SQL Server 2016.
I tried it on various serves where SQL Server 2016 is installed.
Getting either 8623 or 8632 error.
BOL give description for this as below.
Error 8623:
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.
Error 8632:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
It says we need to change the query to avoid such large number of values in IN clause.
We need to use join in this case.
Workaround
As its third party app and we can not modify the query immediately
We changed compatibility level to 120 (SQL Server 2014) and the query worked !!
This gives us time to contact app developer and get the app modified with new
query.
While debugging I found the query which was failing.
The query is like this.
This was working fine SQL Server 2014 but now failing in SQL Server 2016.
I tried it on various serves where SQL Server 2016 is installed.
Getting either 8623 or 8632 error.
BOL give description for this as below.
Error 8623:
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.
Error 8632:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
It says we need to change the query to avoid such large number of values in IN clause.
We need to use join in this case.
Workaround
As its third party app and we can not modify the query immediately
We changed compatibility level to 120 (SQL Server 2014) and the query worked !!
This gives us time to contact app developer and get the app modified with new
query.
No comments:
Post a Comment