stack size

stack size specifies the size, in bytes, of the execution stacks used by each user process on SAP ASE.

Summary Information

Default value

Platform-specific

Range of values

Platform-specific minimum–2147483647

Status

Static

Display level

Basic

Required role

System administrator

Configuration group

User Environment

To find the stack size values for your platform, use sp_helpconfig or sp_configure. stack size must be configured in multiples of 2K. If the value you specify is not a multiple of 2K, sp_configure verification routines round the value up to the next highest multiple.

An execution stack is an area of SAP ASE memory where user processes keep track of their process context and store local data.

Certain queries can contribute to the probability of a stack overflow. Examples include queries with extremely long where clauses, long select lists, deeply nested stored procedures, and multiple selects and updates that holdlock. When a stack overflow occurs, SAP ASE prints an error message and rolls back the transaction. See the Troubleshooting and Error Messages Guide for more information on specific error messages.

The two options for remedying stack overflows are to break the large queries into smaller queries and to increase stack size. Changing stack size affects the amount of memory required for each configured user connection and worker process.

If you have queries that exceed the size of the execution stack, you may want to rewrite them as a series of smaller queries, especially if there are only a small number of such queries, or if you run them infrequently.

There is no way to determine how much stack space a query requires without actually running the query. Stack space for each user connection and worker process is preallocated at start-up.

Therefore, determining the appropriate value for stack size is an empirical process. Test your largest and most complex queries using the default value for stack size. If they run without generating error messages, the default is probably sufficient. If they generate error messages, begin by increasing stack size by a small amount (2K). Re-run your queries and see if the amount you have added is sufficient. If it is not, continue to increase stack size until queries run without generating error messages.

If you are using CIS, or if Java is enabled in the database and you want to use methods that call JDBC, SAP recommends that you increase the default by 50 percent. If you are not using JDBC or CIS, the standard default value is usually sufficient.