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	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	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”.
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 Chapter 3, “Configuring Memory,” in System Administration Guide: Volume 2.
Monitoring	Collecting monitoring information. By default, Adaptive Server does not collect monitoring information. See Chapter 2, “Monitoring Tables,” in the Performance and Tuning Guide: Monitoring 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 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

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.