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.
However, changing timeout values should be a last resort, as there are usually better alternatives to try. For example, make sure statistics exist (by using the show_missing_stats set command) and are up to date, since poor or missing statistics can result is overestimating costs which could result in excessive optimization time as the optimizer tries to find a better plan, even though the current best plan may actually execute quickly. Another solution for reducing compilation time, rather than reducing the timeout, is to turn on the statement cache so that queries that are re-executed frequently are only optimized once and cached. Another solution for complex queries could be to use allrows_oltp, which reduces the options considered during optimization. Yet another solution for reducing compilation time rather than reducing timeout is to use abstract plans. This effectively skips the optimizer and can be used if current performance is acceptable and it is anticipated that the data distribution changes are minimal or will not affect the query plans.
The optimizer triggers timeout during optimization when both these circumstances are met:
At least one complete plan has been retained as the best plan.
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, setting the optimization timeout limit parameter to a value between 0 and 1000. The optimization timeout limit parameter 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.
A separate configuration parameter, sproc optimize timeout limit, is used for stored procedures. It has a default value of 40 and a maximum value of 4000. Since a stored procedure is usually cached, it is worthwhile to spend more time looking for better plans for complex queries, since a procedure is optimized once and then cached for reuse.
A large timeout value may be useful for optimization of stored procedures with complex queries. It is expected that the longer optimization time of the stored procedures will yield better plans; the longer optimization time can be amortized over several executions of the stored procedure.
A small timeout value may be used when a faster compilation time is wanted 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 optimization time at the session level:
set plan opttimeoutlimit <n>
Where n is any integer between 0 and 1000.
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. 0 is used to indicate that no timeout should be used, which could take hours to optimize queries with 20 or more tables if no low cost plan is found.
Summary information |
|
|---|---|
Default value |
10 |
Range of values |
0 – 1000 |
Status |
Dynamic |
Display level |
Comprehensive |
Required role |
System Administrator |
