How do you minimise the number of auxiliary scan descriptors for a table in T-SQL?

Question

I've recently seen occasional problems with stored procedures on a legacy system which displays error messages like this:

Server Message: Number 10901, Severity 17: This query requires X auxiliary scan descriptors but currently there are only Y auxiliary scan descriptors available. Either raise the value of the 'number of aux scan descriptors' configuration parameter or try your query later.

where X is slightly lower than Y. The Sybase manual usefully tells me that I should redesign my table to use less auxiliary scan descriptors (how?!), or increase the number available on the system. The weird thing is, it's been working fine for years and the only thing that's changed is that we amended the data types of a couple of columns and added an index. Can anyone shed any light on this?

Answer 1

You don't say what version of Sybase you are on but the following is good for ASE 12.5 onwards.

I suspect that it's the addition of the new index that's thrown out the query plan for that stored procedure. Have you tried running

update statistics *table_name*

on it? If that fails you can find out how many scan descriptors you have by running

sp_monitorconfig "aux scan descriptors"

and then increase that by running

sp_configure "aux scan descriptors", x

where x is the number of scan descriptors you require.

If you wish to reduce the number of scan descriptors that the store procedure is using then according to here you have to

Rewrite the query, or break it into steps using temporary tables. For data-only-locked tables, consider adding indexes if there are many table scans.

but without seeing a query plan it's impossible to give more specific advice.

Answer 2

This is defect in Sybase 12.5.2 for which a CR was submitted, see issue 361967 in this list. It was patched for 12.5.3 and above.