PRB: Too Many User Connections Keep SQL Server from Starting

  - Microsoft SQL Server version 4.2 for OS/2

SYMPTOMS

Setting memory-related parameters too high can prevent SQL Server from starting properly. Each user connection costs 40K to 42K.

CAUSE

The User Connections parameter represents the single largest demand on machine resources. It equates to the maximum number of connections that SQL Server permits at any one time. (It is NOT directly related to the number of users logged on to the system.) This becomes a problem because each user costs 42K of SQL Server memory that is preallocated at start time. This is documented in the "Microsoft SQL Server System Administrator's Guide." However, the implications of how rapidly this figure multiplies are easily overlooked.

WORKAROUND

The User Connections parameter must be set to the number of open processes expected on SQL Server at any one time. It should be set to the lowest possible value that still accommodates the number of concurrent users you want to support. If users are denied access, you might need to raise the figure slightly. Each DBPROCESS requires one user connection.

MORE INFORMATION

For example, suppose there are 156 workstations on the network and all of them require access to SQL Server on the same machine as the file and print server.

SQL Server is installed on a machine containing 16 MB of RAM. Since this machine is also the file and print server, SQL Server is set with a memory option of 50 percent of installed memory, or 8192K (see the "Microsoft SQL Server Installation Guide," page 24). The procedure cache is left at the default value of 20 percent, and user connections are set to 156 -- a figure that seems reasonable. The SQL server is restarted.

At start-up, SQL Server looks at its configured parameters and allocates 8192K of machine memory for its exclusive use. It then preallocates memory for 156 User Connection structures at 42K each, or 6552K. Of the remaining 1640K, it sets aside 20 percent (or 328K) for compiling stored procedures and related tasks.

The user connections structures plus the procedure cache total 6890K of SQL Server's allocated 8192K, leaving only 1.3 MB for SQL Server to allocate for all other parameters and operations. SQL Server attempts to start, but cannot. As SQL Server tries to do the impossible, endless lines of timing dots appear.

The principal limiting factor in any SQL Server installation is available resources. SQL Server parameters should, therefore, be set to the lowest value that still accommodates users' needs. Although there may be 156 users who need access, the more important question is, how many will actually have an open DBPROCESS at any one time? That is, the number to which the User Connections parameter should be set is the maximum number of expected simultaneous users.