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.
Use sp_configure to set optimization timeout limit at the server level. 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 parameter, optimization 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 “Limiting the time spent optimizing a query”.
