sp_helpconfig

Description

Reports help information on configuration parameters.

Syntax

sp_helpconfig "configname"[, "size"]

Parameters

configname

is the configuration parameter being queried, or a non-unique parameter fragment.

size

is the size of memory, specified by B (bytes), K (kilobytes), M (megabytes), G (gigabytes), or P (pages). Used without the type of size specified, size specifies the number of the entity being configured using this parameter, for examples, locks, open indexes, and so on. size is ignored if configname is not a unique parameter name.

Examples

Example 1

Returns a report on all configuration options that start with “allow”:

sp_helpconfig "allow"
Configuration option is not unique.
 option_name                    config_value run_value
 ------------------------------ ------------ -----------
 allow backward scans                     1            1
 allow nested triggers                    1            1
 allow procedure grouping                 1            1
 allow remote access                      1            1
 allow resource limits                    0            0
 allow sendmsg                            0            0
 allow sql server async i/o               1            1
 allow updates to system tables           0            0

Example 2

Returns a report on how much memory is needed to create a metadata cache for 421 object descriptors:

sp_helpconfig "open objects", "421"
number of open objects sets the maximum number of database objects that are open at one time on SQL Server. The default run value is 500.

Minimum Value  Maximum Value  Default Value  Current Value  Memory Used
-------------  -------------  -------------  -------------  -----------
          100     2147483647            500            500          243

Configuration parameter, 'number of open objects', will consume 207K of memory if configured at 421.

Example 3

Returns a report on how many database descriptors would fill a 1MB database cache:

sp_helpconfig "open databases", "1M"
number of open databases sets the maximum number of databases that can be
open at one time on SQL Server. The default run value is 12.

Minimum Value  Maximum Value  Default Value  Current Value  Memory Used
-------------  -------------  -------------  -------------  -----------
            5     2147483647             12             12          433

Configuration parameter, 'number of open databases', can be configured to
28 to fit in 1M of memory.

Example 4

Returns a report on how many locks will use 512K of memory:

sp_helpconfig "number of locks", "512K"
number of locks sets the number of available locks. The default run value
is 5000.

Minimum Value  Maximum Value  Default Value  Current Value  Memory Used
-------------  -------------  -------------  -------------  -----------
         1000    2147483647           5000           5000           528

Configuration parameter 'number of locks', can be configured to 4848 to fit
in 512K of memory.

Example 5

Returns a report on the status of the allow updates to system tables configuration parameter:

sp_helpconfig "allow updates to system tables"
allow updates to system tables allows system tables to be updated directly.
The default is 0 (off).

Minimum Value  Maximum Value  Default Value  Current Value  Memory Used
-------------  -------------  -------------  -------------  -----------
            0              1              0              0            0

Usage


Planning metadata cache configuration


Using sp_helpconfig with sybdiagdb (Sybase Technical Support only)

NoteSybase Technical Support may create the sybdiagdb database on your system for debugging purposes. This database holds diagnostic configuration data, and is for use by Sybase Technical Support only.

The following configname options have been added to sp_helpconfig for Sybase Technical Support to use with the sybdiagdb database:

For example:

sp_helpconfig "number of ccbs"
Minimum Value Maximum Value Default Value Current Value Memory Used
------------- ------------- ------------- ------------- -----------
0             100             0             0            0

sp_helpconfig "caps per ccb"
Minimum Value Maximum Value Default Value Current Value Memory Used
------------- ------------- ------------- ------------- -----------
5             500            50            50            0

sp_helpconfig "average cap size"
Minimum Value Maximum Value Default Value Current Value Memory Used
------------- ------------- ------------- ------------- -----------
100         10000           200           200            0

Permissions

The options specified in “Using sp_helpconfig with sybdiagdb (Sybase Technical Support only)” can be used only by Sybase Technical Support. Any user can execute sp_helpconfig with other configname options.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

System procedures sp_configure, sp_countmetadata, sp_monitorconfig