For data-only-locked tables of 15 pages or fewer, Adaptive Server does not consider a table scan if there is a useful index on the table. Instead, it always chooses the cheapest index that matches any search argument that can be optimized in the query. The locking required for an index scan provides higher concurrency and reduces the chance of deadlocks, although slightly more I/O may be required than for a table scan.
If concurrency on small tables is not an issue, and you want to optimize the I/O instead, you can disable this optimization with sp_chgattribute. This command turns off concurrency optimization for a table:
sp_chgattribute tiny_lookup_table,
"concurrency_opt_threshold", 0
With concurrency optimization disabled, the optimizer can choose table scans when they require fewer I/Os.
You can also increase the concurrency optimization threshold for a table. This command sets the concurrency optimization threshold for a table to 30 pages:
sp_chgattribute lookup_table,
"concurrency_opt_threshold", 30
The maximum value for the concurrency optimization threshold is 32,767. Setting the value to -1 enforces concurrency optimization for a table of any size. It may be useful in cases where a table scan is chosen over indexed access, and the resulting locking results in increased contention or deadlocks.
The current setting is stored in systabstats.conopt_thld and is printed as part of optdiag output.
