The Parameter Hierarchy

Configuration parameters are grouped according to the area of SAP ASE behavior they affect. This makes it easier to identify all parameters that you might need to tune to improve a particular area of SAP ASE performance.

Although each parameter has a primary group to which it belongs, many have secondary groups to which they also belong. For example, number of remote connections belongs primarily to the network communication group, but it also belongs secondarily to the memory use group. This reflects the fact that some parameters have implications for a number of areas of SAP ASE behavior. sp_configure displays parameters in all groups to which they belong.

Configuration Groups

Parameter Group

Configures SAP ASE for

Backup/Recovery

Backing up and recovering data

Cache manager

Data and procedure caches

Component Integration Services administration

Component Integration Services

DTM administration

Distributed transaction management (DTM) facilities

Diagnostics

Diagnostic principles

Disk I/O

Disk I/O

Error log

Error log, and the logging of SAP ASE events to the Windows event log

Extended stored procedures

The behavior of extended stored procedures (ESPs).

General information

Basic system administration

Java services

Memory for Java in SAP ASE

See Java in SAP Adaptive Server Enterprise for complete information about Java in the database.

If you use method calls to JDBC, you may need to increase the size of the execution stack available to the user.

Languages

Languages, sort orders, and character sets

Lock manager

Locking

Memory use

Memory consumption

Metadata caches

Setting the metadata cache size for frequently used system catalog information. The metadata cache is a reserved area of memory used for tracking information on databases, indexes, or objects. The greater the number of open databases, indexes, or objects, the larger the metadata cache size. For a discussion of metadata caches in a memory-usage context, see System Administration Guide: Volume 2 > Configuring Memory.

Monitoring

Collecting monitoring information. By default, SAP ASE does not collect monitoring information.

See Performance and Tuning Guide: Monitoring and Analyzing > Monitoring Tables.

Network communication

Communication between SAP ASE and remote servers, and between SAP ASE and client programs

O/S resources

Use of operating system resources

Physical memory

Your machine’s physical memory resources

Processors

Processors in an SMP environment

Query Tuning

Query optimization

RepAgent thread administration

Replication via Replication Server

Shared Disk Cluster

Cluster Edition

SQL Server administration

General SAP ASE administration

Security related

Security-related features

Unicode

Unicode-related features

User environment

User environments

The syntax for displaying all groups and their associated parameters, and the current values for the parameters, is:
sp_configure
Note: The number of parameters returned by sp_configure depends on the value to which you have your display level set.
The following is the syntax for displaying a particular group and its associated parameter:
sp_configure "group_name"
For example, to display the disk I/O group, enter:
sp_configure "Disk I/O"
Group: Disk I/O
Parameter Name           Default Memory Used Config Value Run Value
unit         type
--------------           ------- ----------- ------------ ---------
------       -------------
allow sql server async i/o     1           0            1         1
switch       static
diable disk mirroring          1           0            1         1  
switch       static
disk i/o structures          256           0          256       256   
number       dynamic
number of devices             10           0           10        10   
number       dynamic
number of large I/O buffers    6       12352            6         6   
number       dynamic
page utilization percent      95           0           95        95   
percent      dynamic
Note: If the server uses a case-insensitive sort order, sp_configure with no parameters returns a list of all configuration parameters and groups in alphabetical order with no grouping displayed.