Microsoft® SQL Server® 2012 Bible

501

Chapter 19: Confi guring SQL Server

19

Maximum Concurrent User Connections

The user connections option can specify the maximum number of simultaneous user

connections allowed on SQL Server. This option is a self-confi guring option and SQL Server

automatically adjusts the maximum number of user connections as needed, up to a maxi-

mum of 32,767 user connections.

The default for the user connections option is zero, which means that unlimited user connections are allowed.

For most SQL Servers, the default value for the user connections option works best. If you do set this option,

do not set the value too high because each connection has overhead regardless of whether the connection is used.

However, do not set the user connections option to a small value, such as 1 or 2, because this may prevent adminis-

trators from connecting to administer the SQL Server. However, the Dedicated Admin Connection can always connect.

The maximum concurrent user connections option should probably not be set to a given

number of users because applications often open several connections to SQL Server. For

example, ODBC- and ADO-based applications open a connection for every connection object

in code — possibly as many as one for every form, list box, or combo box. Access tends to

open at least two connections.

In Management Studio, the user connections confi guration option can be set by typ-

ing a value from 0 through 32767 in the Max Number of Concurrent Connections box on the

Server Properties Connections tab (refer to Figure 19-9).

The following code sets the maximum number of user connections to 10240:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

EXEC sp_configure 'user connections', 10240;

RECONFIGURE;

SQL Server service must be restarted for the user connections option to take effect.

To determine the maximum number of simultaneous user connections allowed on a SQL

Server instance using code, examine the value in the @@ MAX_CONNECTIONS global vari-

able. The number returned is not the actual number of connections nor is it the confi gured

value; it is the maximum number allowed:

SELECT @@MAX_CONNECTIONS;

Result:

-----------

32767

Query Governor Cost Limit

In the same way that a small gas-engine governor controls the top speed of the engine, the

query governor limits the queries that SQL Server can run according to the estimated query

cost on a specifi c hardware confi guration. If a user submits a query that exceeds the limit

c19.indd 501c19.indd 501 7/30/2012 5:43:01 PM7/30/2012 5:43:01 PM

http://www.it-ebooks.info