The parameter hierarchy

Configuration parameters are grouped according to the area of Adaptive Server behavior they affect. This makes it easier to identify all parameters that you might need to tune to improve a particular area of Adaptive Server 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 Adaptive Server behavior. sp_configure displays parameters in all groups to which they belong.

Table 5-2 lists the configuration parameter groups.

**Table 5-2: Configuration groups**
Parameter group	Configures Adaptive Server for:
Backup/Recovery	Backing up and recovering data
Cache manager	The 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 Adaptive Server events to the Windows Event Log
Extended stored procedures	Affecting the behavior of extended stored procedures (ESPs).
General information	Basic system administration
Java services	Memory for Java in Adaptive Server See the Java in Adaptive Server Enterprise manual 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. See “stack size” for information about setting the stack size parameter.
Languages	Languages, sort orders, and character sets
Lock manager	Locks
Memory use	Memory consumption
Meta-data 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 “Configuring Memory” on page 41.
Monitoring	Collecting monitoring information. By default, Adaptive Server does not collect monitoring information required by the monitoring tables. For more information about the monitoring tables see Chapter 2, “Monitoring Tables,” in the Perfomance and Tuning Guide: Monitorig and Analyzing.
Network communication	Communication between Adaptive Server and remote servers, and between Adaptive Server 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
SQL Server administration	General Adaptive Server administration.
Security related	Security-related features
Unicode	Unicode-related features
User environment	User environments

Table 5-2: Configuration groups

Parameter group

Configures Adaptive Server for:

Backup/Recovery

Backing up and recovering data

Cache manager

The data and procedure caches

Component Integration Services administration

Component Integration Services

DTM administration

Distributed transaction management (DTM) facilities

Diagnostics

Diagnostic principles

Disk I/O

Error log

Error log and the logging of Adaptive Server events to the Windows Event Log

Extended stored procedures

Affecting the behavior of extended stored procedures (ESPs).

General information

Basic system administration

Java services

Memory for Java in Adaptive Server

See the Java in Adaptive Server Enterprise manual 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. See “stack size” for information about setting the stack size parameter.

Languages

Languages, sort orders, and character sets

Lock manager

Locks

Memory use

Memory consumption

Meta-data 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 “Configuring Memory” on page 41.

Monitoring

Collecting monitoring information. By default, Adaptive Server does not collect monitoring information required by the monitoring tables.

For more information about the monitoring tables see Chapter 2, “Monitoring Tables,” in the Perfomance and Tuning Guide: Monitorig and Analyzing.

Network communication

Communication between Adaptive Server and remote servers, and between Adaptive Server 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

SQL Server administration

General Adaptive Server 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

The number of parameters sp_configure returns depends on the value to which you have your display level set. See “User-defined subsets of the parameter hierarchy: display levels” for further information about display levels.

The following is the syntax for displaying a particular group and its associated parameter, where group_name is the name of the group you are interested in:

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

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.