Microsoft KB Archive/67619

INF: SQL Memory Configuration Minimization ID Number: Q67619

1.00 1.10 OS/2 docerr

Summary:

You cannot reduce the memory requirements to the 1000 page (or 2000K) minimum specified in the SAF Config Menu option in SQL Server versions 1.0 and 1.1.

More Information:

The following figures are values that were obtained in a laboratory while attempting to reduce the amount of SQL Server memory to the minimum specification of 1000 pages (or 2000K).

SQL Server Version 1.1 Configuration Minimizations
These parameters are changeable from within SAF, using the Menu option (on the Config menu), or through ISQL, using the sp_configure stored procedure. Although the option screen states that a minimum memory value of 1000 pages (2000K) is attainable, lab tests could not reduce the memory below 1196 pages (2392K), even with all memory-intensive parameters minimized.

WARNING: Pushing the parameters to these low limits will definitely have a crippling effect on performance and lead to severe thrashing. If all parameters are set to minimums or maximums, SQL Server will demand an infinite amount of time to start.

We recommend that you use the parameters set at the time of installation unless you are thoroughly familiar with how they interact. (For example: For each user connection you add, SQL Server demands an additional 40K of memory.)

SQL Server Memory Options Range ————————- —–

User connections   [5]      5 to 1024 users Each increment demands about 38K (37.3K was the lab average) Memory [2392K] 2000K to 28,000K 2392K = 1196 Pages (the lowest value obtained in the lab) Procedure cache    [99%]    1 to 99 percent Each decrement demands 2K (this is opposite of expectations)

SQL Server General Options
Open databases     [5]      5 to 100 databases Two increments demand 2K (1K average per increment)

Open Objects       [120]    100 to 10,000 objects Thirty increments demand 2K (66 average per increment)

Locks              [5000]   5000 to 50,000 locks 64 increments demand 2K (31.25K average per increment)

*Fill Factor       [0]      0 to 100 percent full pages

*Time slice        [100]    50 to 1000 milliseconds

*Database size     [2]      2 MB to 10,000 MB                              (default value for each new                              database)

*Media retention   [0]      0 to 365 days

*Recovery flags    [0]      0 (short) or 1 (verbose) (Determines how much information                             will be displayed during recovery.                              Short is for headers only. Verbose                              is for each individual transaction.)

SQL Server Dynamic Options
*Recovery interval [5]      1 to 32767 minutes

*Allow updates     [0]      0 (no) or 1 (yes) (*) denotes a parameter that places virtually no demand on memory.

Installation
Multi-user SQL Server version 1.1 (update) on OS/2 version 1.21 and OS/2 LAN Manager version 2.0a.