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, use sp_chgattribute to disable this optimization. For example, to turn off concurrency optimization for a table:
sp_chgattribute tiny_lookup_table, “concurrency_opt_threshold”, 0
With concurrency optimization disabled, the query processor 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; this setting may be useful when 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.
