You can use the optimization timeout limit configuration parameter to restrict the amount of time Adaptive Server spends optimizing a query. optimization timeout limit specifies the amount of time Adaptive Server can spend optimizing a query as a percentage of the total time spent processing the query.
The timeout is activated only if:
At least one complete plan has been retained as the best plan, and
The optimization timeout limit has been exceeded.
Set optimization timeout limit at the server level using sp_configure. For example, to limit optimization time to 10 percent of total query processing time, enter:
sp_configure “optimization timeout limit”, 10
To set optimization timeout limit at the session level, use:
set plan optimeoutlimit n
This command overrides the server setting.
The default value is 10 percent; you can specify any value from 1 to 1000.
At the server level, there is a separate configuration, sproc optimize timeout limit, for the server level default timeout value within stored procedure compilations. The default value is 40 percent; you can specify any value from 1 to 4000.
For more information about optimization timeout limit, see the chapter “Abstract Plans” in the Query Processor guide.
