INF: Too Many User Connections Keep SQL Server from Starting

ID Number: Q75288

1.10 1.11 4.20

OS/2

Summary:

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.

RESOLUTION

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.