sp_helpconfig

Currently, the estimate parameter works only with the compression info pool size configuration parameter.

Syntax

sp_helpcofig "config_name" 
    [, { "size" | "estimate [using_argument = value [, using_argument = value ] [, ...] ] } ]

Parameters

• estimate – recommends a value to which you can set the indicated configuration parameter, based on the settings of other configuration parameters or user-specified values that override those settings.
• using_argument = value – provides these additional arguments for the estimate parameter to override default values for configuration parameters that affect the estimated memory:
  • maxconcusers = value – specifies the maximum number of concurrent users, as an integer, that can access compressed tables.

    For example, maxconcusers = 0.7 indicates 70 percent of the configured value for number of user connections. An integer value of 1 or greater specifies an absolute number of concurrent users.

  • numcolumns = value – specifies the average number of columns in a compressed table.
  • numcompobjs = value – specifies the default number of open objects as an integer, or as a percentage, that require memory for compression metadata. For example, numcompobjs = 0.2 indicates that 20 percent of the configured value for number of open objects. An integer value of 1 or greater specifies an absolute number of open objects.
  • numtables = value – determines the average number of compressed tables accessed in a statement.

  Issuing sp_helpconfig without arguments generates usage information, showing the subclauses you may specify, and some examples of typical usage.

Examples

• Example 1 – shows the sp_helpconfig ... estimate parameter run from a system database (such as master or tempdb). In this example, sp_helpconfig performs the estimate using default values for factors that affect the required memory:

  sp_helpconfig 'compression info pool', 'estimate'
  The compression information pool size parameter indicates the 
  amount of memory currently available to store table compression
  information.
  
  Minimum Value  Maximum Value  Default Value  Current Value
       Memory Used     Unit              Type
  -------------  -------------- -------------  -------------
       -----------     ----------------  -------
              0     2147483647          4096           4096
             8240      memory pages(2k)  dynamic
  
  Estimated memory required for 600 concurrent users requesting
  memory from this pool, accessing 500 compressed objects with 
  50 columns, on an average, per compressed table is 22600 KB.
  
  Configuration parameter, 'compression info pool size', can be
  configured to 21971 to fit in 44200K of memory.

• Example 2 – overrides the defaults with site-specific parameters to estimate the memory and configuration value setting. sp_helpconfig is executed a second time from a system database (such as master or tempdb) to estimate the memory required for server-wide concurrent access to compressed objects, when these objects are accessed from multiple databases in the server:

  sp_helpconfig 'compression info pool', 'estimate
   using numcompobjs=0.3, numtables=2.25, numcolumns=25,
   maxconcusers=0.85'
  
  The compression information pool size parameter indicates the 
  amount of memory currently available to store table compression
  information.
  
  Minimum Value  Maximum Value  Default Value  Current Value
       Memory Used     Unit              Type
  -------------  -------------- -------------  -------------
       -----------     ----------------  -------
              0     2147483647           4096           4096
           8240        memory pages(2k)  dynamic
  
  Estimated memory required for 1020 concurrent users requesting
  memory from this pool, accessing 150 compressed objects with 25 
  columns, on an average,
   per compressed table is 37020 KB.
  
  Configuration parameter, 'compression info pool size', can be 
  configured to 18402 to fit in 37020K of memory.

• Example 3 – shows sp_helpconfig ... estimate run against a user database with numerous compressed tables, which are used frequently by an application. The server is configured as:

  sp_configure 'user connections', 900
  sp_configure 'worker processes', 500
  sp_configure 'max parallel degree', 5

  In this example, estimate gathers metrics from the user database from which you issue the procedure for:

  • The number of compressed objects
  • The average number of columns in these compressed objects
  Using these input values, sp_helpconfig estimates the memory required for compression info pool to store table compression information:

  sp_helpconfig 'compression info pool size', 'estimate'
  
  The compression information pool size parameter indicates 
  the amount of memory currently available to store table 
  compression information.
  
  Minimum Value  Maximum Value  Default Value  Current Value
       Memory Used     Unit              Type
  -------------  -------------- -------------  -------------
       -----------     ----------------  -------
              0      2147483647          4096          15396
             33384     memory pages(2k)  dynamic
  
  Estimated memory required for 1400 concurrent users requesting
  memory from this pool, accessing 78240 compressed objects
  with 10 columns, on an average, per compressed table is 74850 KB.
  
  Configuration parameter, 'compression info pool size', can be
  configured to 34519 to fit in 74850K of memory.

  This output indicates that a total of 1400 concurrent users are expected to simultaneously request memory. The database has slightly more than 78000 compressed objects, with each table having, on average, 10 columns. The estimated value for this configuration option is 34519.

  However, if not all the objects are routinely accessed simultaneously, and not all the configured user connections are simultaneously active, you can refine the estimates by providing site-specific overrides with the using parameter subclause:

  sp_helpconfig 'compression info pool size', 'estimate
  using numcompobjs = 50000, maxconcusers=600'
  
  The compression information pool size parameter indicates
  the amount of memory currently available to store table
  compression information.
  
  Minimum Value  Maximum Value  Default Value  Current Value
       Memory Used     Unit              Type
  -------------  -------------- -------------  -------------
       -----------     ----------------  -------
              0      2147483647          4096          15396
             33384     memory pages(2k)  dynamic
  
  Estimated memory required for 1100 concurrent users requesting
  memory from this pool, accessing 50000 compressed objects with
  10 columns, on an average, per compressed table is 55225 KB.
  
  Configuration parameter, 'compression info pool size', can be
  configured to 25468 to fit in 55225K of memory.

  In this output, maxconcusers = 600 implies that 600 concurrent client connections are accessing compressed objects requesting memory. Because of the parallel configuration settings, sp_helpconfig estimates that a total of 1100 requesters may concurrently request memory. The estimated value for this configuration option is 25468.