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. sp_helpconfig is 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 so that it is a unique name and the value you want to configure:
1> 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 to use 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 to indicate to the procedure that the value is a size, not a configuration value. The valid units of measure are:
P – pages, (Adaptive Server 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 Adaptive Server is not able to calculate the memory use. sp_helpconfig prints an error message in these cases. 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.
