patternsqlMinor
query processor ran out of internal resources and could not produce a query plan
Viewed 0 times
produceprocessorqueryandcouldplanraninternalresourcesnot
Problem
This is showing up in the logs several times a night. How do I find the query causing the issue? SQL Server 2008 R2 Sp1.
Solution
Look for queries with very long
Occasionally the issue can be resolved by applying a product update (service pack or cumulative update) or enabling a supported trace flag, but more often the fundamental issue is the unusual SQL generated by some tools or data abstraction layers. The latter will require application changes, unfortunately.
Enabling documented trace flags 4102, 4118, 4122 (or the covering 4199) may also avoid the issue you are seeing. Review the documentation to see if they address the root cause in your case:
Microsoft Knowledge Base article for TF 4122
Microsoft Knowledge Base article for TF 4102, 4118
Microsoft Knowledge Base article for TF 4199
IN lists, a large number of UNIONs, or a large number of nested subqueries. These are the most common causes of this particular error message in my experience.Occasionally the issue can be resolved by applying a product update (service pack or cumulative update) or enabling a supported trace flag, but more often the fundamental issue is the unusual SQL generated by some tools or data abstraction layers. The latter will require application changes, unfortunately.
Enabling documented trace flags 4102, 4118, 4122 (or the covering 4199) may also avoid the issue you are seeing. Review the documentation to see if they address the root cause in your case:
Microsoft Knowledge Base article for TF 4122
Microsoft Knowledge Base article for TF 4102, 4118
Microsoft Knowledge Base article for TF 4199
Context
StackExchange Database Administrators Q#28945, answer score: 6
Revisions (0)
No revisions yet.