Long-running and complex queries can be time-consuming and costly to optimize. The timeout mechanism helps to limit that time while supplying a satisfactory query plan. The query optimizer provides a mechanism by which the optimizer can limit the time taken by long-running and complex queries; timing out allows the query processor to stop optimizing when it is reasonable to do so.
The optimizer triggers timeout during optimization when both these circumstances are met:
At least one complete plan has been retained as the best plan, and
The user configured timeout percentage limit has been exceeded.
You can limit the amount of time Adaptive Server spends optimizing a query at every level, using the optimization timeout limit parameter, which you can set to anyvalue between 0 and 1000. optimization timeout limit represents the percentage of estimated query execution time that Adaptive Server must spend to optimize the query. For example, specifying a value of 10 tells Adaptive Server to spend 10% of the estimated query execution time in optimizing the query. Similarly, a value of 1000 tells Adaptive Server to spend 1000% of the estimated query execution time, or 10 times the estimated query execution time, in optimizing the query.
See Chapter 5, “Setting Configuration Parameters,” in the System Administartion Guide for more information about optimization timeout limit.
A large timeout value may be useful for optimization of stored procedures with complex queries. Generally, longer optimization time of the stored procedures yields better plans; the longer optimization time can be amortized over several executions of the stored procedure.
A small timeout value may be used when you want a faster compilation time from complex ad hoc queries that normally take a long time to compile. However, for most queries, the default timeout value of 10 should suffice.
Use sp_configure to set the optimization timeout limit configuration parameter at the server level. For example, to limit optimization time to 10% of total query processing time, enter:
sp_configure “optimization timeout limit", 10
Use set to set timeout at the session level:
set plan opttimeoutlimit <n>
where n is any integer between 0 and 4000.
Use select to limit optimization time at the query level:
select * from <table> plan "(use opttimeoutlimit <n>)"
where n is any integer between 0 and 1000.
