Optimizer Timeout
When the query processor finds itself consuming a lot of time optimizing a query, it may decide to stop the optimization process abruptly, and choose the best available plan. This is to ensure that the optimizer doesn’t end up optimizing forever. This is called optimizer timeout (based on the number of plans considered relative to the cost of the best plan so far).
Optimizer memory abort
When queries become more complex number of potential plans to consider can quickly grow in thousands. Optimizer has limit for memory it is allowed to use , when the optimizer reaches the limit it ends with optimizer memory abort.
When timeout or memory abort happens optimizer might choose the best plan from plans which was generated till timeout or abort and it might be far from optimal plan so the query execution can take long time and consume resource.
On SQL 2000 and earlier the only way to detect this condition is compiling the query with trace flag 8675. If one of these conditions occur the output will reflect a timeout abort or memory abort, similar to the following:
End of simplification, time: 2.869 net: 2.869 total: 2.869 net: 2.869
end exploration, tasks: 200094 no total cost time: 16.17 net: 16.169 total: 19.04 net: 19.039
*** Optimizer time out abort at task 614400 ***
*** Optimizer time out abort at task 614400 ***
Msg 8623, Level 16, State 1, Line 3
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.
End of simplification, time: 0.156491 elapsed: 0.156491
end exploration, tasks: 1614 no total cost time: 0.552436 elapsed: 0.708927
end search(0), cost: 1275.32 tasks: 3888 time: 0.195008 elapsed: 0.903935
end exploration, tasks: 7596 Cost = 1275.32 time: 0.548032 elapsed: 1.45197
end search(1), cost: 1263.15 tasks: 21985 time: 2.30564 elapsed: 3.75761
*** Optimizer memory usage abort ***
End of optimization, elapsed: 2.98304
From SQL server 2005 to determine whether the query optimizer times out or MemoryLimitExceeded search for the
StatementOptmEarlyAbortReason="TimeOut" (or) StatementOptmEarlyAbortReason="MemoryLimitExceeded" expression in the XML plan output.
We can avoid optimizer from timing out and picking bad plan by enabling trace flag –T8780. This increases the time limit before the timeout occurs.
Note: Don’t enable this trace flag at server level , enable it only for the session which runs the query and identify if the optimizer is picking up a better plan. If you see optimizer picking up the better plan, right approach is to tune the query manually or using DTA and apply the recommendations . You can use this trace flag till you apply the recommendations made by DTA.
If you experience “ Optimizer memory usage abort” use “SQLServer:Memory Manager\Optimizer Memory (KB)” counter to the amount used for compilation .
select * from sys.dm_os_memory_clerks where type=’MEMORYCLERK_SQLOPTIMIZER’ will tell us the overall memory used by optimizer.
We can also use the CompileMemory= expression in XML plan output starting from SQL server2005 SP2 which will give us the compile memory used by individual plans. If you find optimizer memory is very low then identify what is contributing to memory contention in SQL Server and tune it.
I will discuss compile memory in detail when I blog about Resource_semaphore wait types.
Note: You may also receive below error because of few known issues documented in KB articles 982376, 946020,926773,917888 so if none of the fixes resolve the issue you may have to follow the same steps documented above.
{
"Msg 8623, Level 16, State 1, Line 1
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."
}
If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/
Thank you,
Karthick P.K |My Facebook Page |My Site| Blog space| Twitter
MSSQLWIKI 