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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
migrated from stackoverflow.com Nov 19 '12 at 20:49
This question came from our site for professional and enthusiast programmers.
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:
I have solved a similar issue by using a server-side startup 'tuning' trace that runs in the background capturing statements running for over 1 second (you could perhaps set it to 10secs if you have an extremely busy server).
When the Query Processor fails to produce a plan it takes over 10 seconds to do so (in my experience)
The Errorlog entry does show the SPID involved along with the exact time, so going to the 'tuning' trace it is easy to identify the offending statement.
Surpisingly it may have a 'success' errorcode.
I received this error because of another reason which I didn't see online, so I'm posting the problem and solution here.
This can happen when trying to modify an xml column by inserting a very large text. For example...
To fix it, you can use a sql variable to hold the large text
The method seems faster anyway so should probably be used whenever possible
Possible it is not one query causing the issue. If you are using a ton of ad-hoc queries it would be prudent to enable 'optimize for ad-hoc workloads'. This way SQL Server will only create plans the second time a query is executed.
You can check using below SQL (Reference here):