Using sp_helpconfig

sp_helpconfig estimates the amount of memory required for a given configuration parameter and value. It also provides a short description of the parameter, information about the minimum, maximum, and default values for the parameter, the run value, and the amount of memory used at the current run value.

You may find sp_helpconfig particularly useful if you are planning substantial changes to a server, such as loading large, existing databases from other servers, and you want to estimate how much memory is needed.

To see how much memory is required to configure a parameter, enter enough of the parameter name to make it unique, and the value you want to configure:

sp_helpconfig "worker processes", "50"

number of worker processes is the maximum number of worker processes that can be
in use Server-wide at any one time.

Minimum Value  Maximum Value  Default Value  Current Value  Memory Used 
Unit     Type
-------------  -------------  -------------  -------------  ----------
-------  ------------
0              2147483647     0              0             0
number   dynamic

Configuration parameter, 'number of worker processes', will consume 7091K of memory if configured at 50.
Changing the value of 'number of worker processes' to '50' increases the amount of memory ASE uses by 7178 K.

You can also use sp_helpconfig to determine the value for sp_configure, if you know how much memory you want to allocate to a specific resource:

1> sp_helpconfig "user connections", "5M"

number of user connections sets the maximum number of user connections that can
be connected to SQL Server at one time.
Minimum Value  Maximum Value  Default Value  Current Value  Memory Used
Unit     Type
-------------  -------------  -------------  -------------  ----------
-------  ------------
5              2147483647     25             25             3773
number  dynamic
Configuration parameter, 'number of user connections', can be configured to 33
to fit in 5M of memory.

The important difference between the syntax of these two statements is the use of a unit of measure in the second example (the “M” for megabytes) to indicate to sp_helpconfig that the value is a size, not a configuration value. The valid units of measure are:

P – pages (SAP ASE 2K pages)
K – kilobytes
M – megabytes
G – gigabytes

There are some cases where the syntax does not make sense for the type of parameter, or where SAP ASE cannot calculate memory use. In these cases, sp_helpconfig prints an error message. For example, if you attempt to specify a size for a parameter that toggles, such as allow resource limits, sp_helpconfig prints the message that describes the function of the parameter for all the configuration parameters that do not use memory.