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

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

<p>where <em>X</em> is slightly lower than <em>Y</em>. 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?</p>

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.