How do you minimize the number of scan helper declarations for a table in T-SQL?
I recently saw occasional problems with stored procedures on a legacy system that displays error messages like this:
Server Message: Number 10901, Severity 17: This request requires an X auxiliary scan descriptor, but currently only Y auxiliary scan descriptors are available. Either raise the "number of aux scan descriptors" config value or try the request later.
where X is slightly lower than Y. The Sybase manual is helpful in telling me that I should redesign my spreadsheet to use fewer scan helper definitions (how ?!) or to increase the number available on the system. The weirdest thing is that it has been working great for years and the only thing that has changed is that we changed the data types from multiple columns and added an index. Can anyone shed some light on this?
source to share
You don't say which version of Sybase you are using, but the following is for ASE 12.5 onwards.
I suspect it was the addition of a new index that rejected the query plan for this stored procedure. Have you tried to run
update statistics *table_name*
On him? If that fails, you can find out how many scan descriptors you are using by running
sp_monitorconfig "aux scan descriptors"
and then increase this by running
sp_configure "aux scan descriptors", x
where x is the number of required scan descriptors.
If you want to reduce the number of scan descriptors that the repository procedure uses, then according to here you should
Rewrite the query or split it into steps using temporary tables. For tables with data-only locking, consider adding indexes if there are many table scans.
but without seeing the query plan, it is impossible to give more specific advice.
source to share