Reports help information on configuration parameters.
sp_helpconfig "configname", ["size"]
is the configuration parameter being queried, or a non-unique parameter fragment.
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.
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
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.
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.
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.
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
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.
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.
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.
Use sp_helpconfig when you are planning a metadata cache configuration for a server.
For example, suppose you were planning to move a database that contained 2000 user indexes to a different server. To find how much memory you would need to configure for that server so that it would accommodate the database’s user indexes, enter the following command:
sp_helpconfig "open indexes", "2000"
number of open indexes sets the maximum number of indexes that can be 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 208 Configuration parameter, ’number of open indexes’, will consume 829k of memory if configured at 2000.
Alternatively, suppose you had 1MB of memory available for the index cache, and you needed to know how many index descriptors it would support. Run the following command:
sp_helpconfig "open indexes", "1M"
number of open indexes sets the maximum number of indexes that can be 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 208 Configuration parameter ’number of open indexes’, can be configured to 2461 to fit in 1M of memory.
Based on this output, if you have 1MB of memory, you can create an index descriptor cache that can contain a maximum of 2461 index descriptors. To create this cache, set the number of open indexes configuration parameter as follows:
sp_configure "number of open indexes", 2461
Sybase
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:
number of ccbs – the number of configurable action point control blocks available to aid debugging.
caps per ccb – the maximum number of configurable action points that can be configured at any one time within one configurable action point.
average cap size – the estimated number of bytes of memory required to store the information associated with a typical configurable action point.
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
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.
System procedures sp_configure, sp_countmetadata, sp_monitorconfig
