Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thank you

share|improve this question

migrated from stackoverflow.com Nov 19 '12 at 20:49

This question came from our site for professional and enthusiast programmers.

    
you are right, but the spread on this problem mainly led me to the mentioned problem. So it was a bad comment from me ... I'll delete it. –  bummi Nov 19 '12 at 19:31
    
I can use extended events to trap the sqltext but I don't know which event to use. Any suggestions? –  SQLSpartan Nov 19 '12 at 19:38
    
@SQLSpartan sqlserver.error_reported should do it I would have thought. –  Martin Smith Nov 19 '12 at 19:43
1  
I would suggest reading Jonathan's blog for information on why you might not want to rely on sql_text: sqlskills.com/blogs/jonathan/post/… –  Aaron Bertrand Nov 19 '12 at 21:27
    
What is the max memory setting and available RAM on the server ? You may want to refer this msdn.microsoft.com/en-us/library/ms177682(v=sql.100).aspx and This connect item connect.microsoft.com/SQLServer/feedback/details/521943/… Note: SQL Server 2008 R2 CU3 has a possible FIX: A non-yielding scheduler error or an error 8623 occurs when you run a query that contains a large IN clause in SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2. –  Kin Apr 25 '13 at 21:30

3 Answers 3

Look for queries with very long 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

share|improve this answer

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.

share|improve this answer

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):

SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
go
share|improve this answer
    
-1 This is incorrect. –  Paul White May 28 '13 at 4:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.