number of sort buffers

number of sort buffers specifies the amount of memory allocated for buffers used to hold pages read from input tables and perform index merges during sorts. number of sort buffers is used only for parallel sorting.

Summary Information

Default value

500

Range of values

0–32767

Status

Dynamic

Display level

Comprehensive

Required role

System administrator

Configuration group

SQL Server Administration

SAP ASE allocates sort buffers from the cache to which the table is bound. If the cache is not bound to a table, SAP ASE allocates the sort buffers from the cache to which the database is bound. If the cache is not bound to a table or a database, SAP ASE allocates sort buffers from the default data cache.

Parallel sorts are used when you:

See Performance and Tuning Series: Query Processing and Abstract Plans > Using Statistics to Improve Performance.

The value you use for number of sort buffers depends on the page size of the server.

SAP recommends that you leave this parameter set to the default except when you are creating indexes in parallel.

Setting the value too high can rob nonsorting processes of access to the buffer pool in caches being used to perform sorts.

If you configure a high number of sort buffers, a sort on a large table may require more procedure cache. The effect is more pronounced with tables that have smaller row sizes, because the number of rows per page is higher.

This equation estimates the amount of procedure cache required (in bytes):

(Number of sort buffers) X (rows per page) X 100

If you do not configure enough procedure cache for the number of sort buffers, the sort may fail with error message 701. If this occurs, reconfigure SAP ASE with a lower number of sort buffers and retry the sort.