Microsoft SQL Server 2008 R2 Unleashed- P236:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 2314 CHAPTER 55 Configuring Tuning and Optimizing SQL Server Options are limited to that maximum number of user connections until you specify a larger value. If you specify a value other than 0 the memory allocation for user connections is allocated at SQL Server startup time and it burns up portions of the memory pool. Each connection takes up 40KB of memory space. For instance if you configure SQL Server for 100 connections SQL Server pre-allocates 4MB 40KB x 100 for user connections. You can see that setting this value too high might eventually impact performance because the extra memory could instead be used to cache data. In general user connections are best left to be self-configuring. The following is an example of this option exec sp_configure user connections 300 go RECONFIGURE go In Figure you can see the current setting of 0 unlimited for the user connections value within SSMS. If you plan to set this option the value must be between 5 and 32 767. FIGURE The Connections page of the Server Properties dialog in SSMS. Configuration Options and Performance 2315 user options Type Basic Default value 0 The user options parameter allows you to specify certain defaults for all the options allowed with the SET T-SQL command. Individual users can override these values by using the SET command. You are essentially able to establish these options for all users unless the users override them for their own needs. User options is a bitmask field and each bit represents a user option. Table outlines the values you can set with this parameter. TABLE Specifying User Options Values Bitmask Value Description 1 DISABLE_DEF_CNST_CHK controls interim deferred constraint checking. 2 IMPLICIT_TRANSACTIONS controls whether a transaction is started implicitly when a statement is executed. 4 CURSOR_CLOSE_ON_COMMIT controls the behavior of cursors after a commit has been performed. 8 ANSI_WARNINGS controls truncation and nulls in aggregate warnings. 16 .
