Optimizer Timeout or Optimizer memory abort
Posted by Karthick P.K on October 7, 2012
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 
Prashant Kumar said
Nice post! I see you mentioned XML plan output to look for CompileMemory. OQ timeout is also reported in XML plan and that’s probably the best way to determine it.
Karthick P.K said
Yes, I did mention about it “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.”
Prashant Kumar said
Okay that seems to be WP formatting issue then 🙂
The line is truncated above in the actual post.
Karthick P.K said
You are correct Prashant . When I initially posted that line got truncated.Then i edited
Query optimization « MSSQLWIKI said
[…] Optimizer Timeout or Optimizer memory abort […]
piano playing tips said
What’s Taking place i’m new to this, I stumbled upon this I’ve discovered It absolutely useful and it has aided me out loads. I’m hoping to give a contribution & assist other
customers like its helped me. Good job.
webpage said
What a information of un-ambiguity and preserveness of precious knowledge concerning
unpredicted feelings.
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said
[…] Optimizer Timeout or Optimizer memory abort […]
CRM SQL Query go faster trick – SQL Server trace flag 8780 – Hosk's Dynamic CRM Blog said
[…] Optimizer Timeout or Optimizer memory abort […]
CRM SQL Query go faster trick – SQL Server trace flag 8780 - Microsoft Dynamics CRM Community said
[…] Optimizer Timeout or Optimizer memory abort […]
anil said
Hi Karthick,
Thank you for sharing wonderful post !!
Do you think executing same complex query with MAXDOP 1 can be of help ? Also does the size of cached plans in buffer pool and overall memory usage of server also affect how many plans can be considered before abort happens?
Appreciate your response. Thank you.
Br,
Anil kumar
SQL DBA
8623错误：The query processor ran out of internal resources and could not pro – 服务器代维,服务器维护,服务器安全维护,服务器企业级维护,服务器安全 said
[…] https://mssqlwiki.com/2012/10/07/optimizer-timeout-or-optimizer-memory-abort/ […]
PotapProox said
Всю ночь осматривал данные сети, при этом к своему восторгу заметил отличный ресурс. Вот гляньте: лучшие съемные протезы . Для меня данный сайт явился довольно полезным. Всего доброго!
Jackciz said
Prednisone
Prednisone
http://www.healthrpose.com/prednisone/
buy Prednisone
Prednisone
ZaharRal said
Минуту назад разглядывал материалы сети интернет, неожиданно к своему удивлению увидел прекрасный сайт. Я про него: http://365xxx.net/ . Для нас данный сайт показался довольно полезным. Всего наилучшего!
JesseVop said
جريدة الاتحاد
فاذا الذي بينك وبينه عداوة
يوم الطفل
read to know
الرجل الاخر
جريدة الاتحاد
خسوف القمر اليوم
BlakeVop said
جريدة الاتحاد العراقية
خسوف القمر اليوم
نبضات القلب اقل من
جرائد العراقية اليومية
نبضات القلب اقل من
فاذا الذي بينك وبينه عداوة
نبضات القلب اقل من
goodlive1987 said
http://hantutoto.info
InnaSware said
http://bitly.com/2HaETIi
засмотреться в хорошем качестве
ухаживать полностью
уставиться онлайн для русском
смотреть онлайн entirely hd jammed hd
засмотреться онлайн hd смотреть онлайн hd 720p
созерцать онлайн hd 1080p глядеть онлайн в hd качестве
заглядеться в хорошем качестве онлайн
DenkiVenda said
dhcnhkdjyyg ohpj oqyybk ahlvuoumbus
Melvincah said
Праздники и дни рождения, которые мы отмечаем, чаще всего не проходят без цветов. Они оставляют красочные воспоминания о каждой дате. У любого человека имеется цветок, которому он отдаёт предпочтение из большого разнообразия. В нашем широком цветочном ассортименте вы найдете цветы на любой вкус.
Если вы не уверены в цветочных предпочтениях человека, которому приобретаете цветы, можете остановиться на красивейших букетах. Наши букеты сделаны профессиональными флористами. Букет из ярких роз, нежных орхидей, утонченных хризантем и других, удивляющих особой красотой цветов, будет чудесным подарком, как даме, так и джентльмену. Если вы хотите доставить радость девушке, то купите к букетунапример мягкую игрушку. Подобный сюрприз станет по душе каждой представительнице слабого пола.
Розы являются самыми покупаемыми цветами. Даря розы, вы наверняка угодите любому человеку. Эти прекрасные цветы имеют уникальный аромат, который будет радовать длительное время. На нашем складе имеется большой выбор сортов роз разнообразной длины и цветовой гаммы.
цветы в санкт-петербурге
На вопросы относительно подбора букета или создания его по индивидуальному заказу ответят наши специалисты.
DavidKneek said
травы для чистки печени – синусовая аритмия, профилактика сахарного диабета
tyffresy said
Это система вариаций была, но видно исказилась
Естьбольшое количество причин делать покупки в реальных магазинах. Это и оценка качества товара визуально, и возможность примерки, и другие. Но если вопрос в экономии, заказе товара, которого нет в вашем городе. Да что обсуждать, ассортимент в интернет магазинах намного шире. Такжемногие из них предлагают возможность возврата если товар не подошел. В общем сохраняют много сил на поездки по магазинам. Еще несколько аргументов в пользу интернет магазина можно прочитать в статье http://interesu.ru/index.php/vozmozhnosti-interneta/1161-pokupki-v-internete , Покупки в Интернете. А вы как считаете?
Is it possible to give the optimizer more or all time it needs? said
[…] it just gives it more time (not unlimited, as described in detail here (russian) and less detailed here) to do its […]