sp_helpconfig

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 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

• sp_helpconfig reports help information on configuration parameters, such as how much memory would be needed if the parameter were set to a certain value. sp_helpconfig also displays the current setting, the amount of memory used for that setting, the default value, and the minimum and maximum settings.

  Note: The “maximum value” setting refers to the largest number that the parameter’s datatype can accept, rather than to an actual configurable value.

  In many cases, the maximum allowable values for configuration parameters are extremely high. The maximum value for your server is usually limited by available memory and other resources, rather than by configuration parameter limitations.

• cluster options displays all strictly cluster-wide configuration options.

• If system_view is set to cluster, sp_helpconfig displays configuration information for all instances in the cluster.

• If system_view is set to instance, sp_helpconfig displays configuration information for the current instance.

• If you use a nonunique parameter fragment for configname, sp_helpconfig returns a list of matching parameters with their configured values and current values. See Example 1.

• sp_helpconfig accepts static, dynamic, and read-only options.

• sp_helpconfig 'restricted decrypt permission' returns the following display:

  sp_helpconfig 'restricted decrypt permission'

  0 - restricted decrypt permission disabled (default).
  1 - restricted decrypt permission enabled
  
  Minimum Value  Maximum Value  Default Value  Current Value
      Memory Used  Unit     Type
  -------------  -------------  -------------  --------------
      -----------  -------------
  0                          1              0              0
                0  switch   dynamic 

Permissions

Any user can execute sp_helpconfig except the following, which requires sybase_ts_role.

Permission checks do not differ based on the granular permissions settings.

Auditing

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