6

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.

0

4 Answers 4

6

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

2

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.

1

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...

update MyTable
set XmlColumn.modify('
insert
    Very large text here...
after (/RootNode/Node)[1]')
where Id = 1

To fix it, you can use a sql variable to hold the large text

declare @MyXml xml
set @MyXml = 'Very large text here...'
update MyTable
set XmlColumn.modify('
insert
    sql:variable("@MyXml")
after (/RootNode/Node)[1]')
where Id = 1

The method seems faster anyway so should probably be used whenever possible

-1

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.