Estimate Memory Requirements for compression info pool size

Use the estimate parameter to determine the approximate amount of memory required for the compression info pool size configuration parameter.

The estimate parameter 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:

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

using_argument= value provides these additional arguments for the estimate parameter to override default values:

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.

This example 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.

This example 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.

This example 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.