Configuration Options and Performance


This section explains essential information about most of the SQL Server configuration options and their impact on SQL Server performance. Many of the options don't have performance implications, but they will be discussed anyway. As part of each option's explanation, an indication of whether the option is Advanced or Basic will be given along with the option's default value and whether the option is self-configuring or not. Possible values will also be listed depending on the generalized application processing types that were identified earlier.

Affinity Mask

Type ”Advanced option

Default value ”

SQL Server supports symmetric multiprocessing (SMP). SMP support means that a thread is not tied to a particular processor on the machine. This allows SQL Server to run multiple threads simultaneously , resulting in a high level of load balancing across processors. A value of (the default) allows Windows Scheduling algorithm to set the threads affinity. This qualifies the affinity mask as self-configuring by the operating system. However, when a server is experiencing a heavy load because of other applications running on the same server, it might be desirable to bind thread affinity to a processor.

The affinity mask is a bitmapped field. Starting from the least significant digit, each bit that is set to 1 represents the processor on which SQL Server will spawn its threads. Processors are numbered from to 7 . An example of the bit mask values for the first seven processors of an eight-processor system follows ; decimal values are shown in parentheses:

Bit Mask Processors Used
00000001 (1)
00000011 (3) 0,1
00000111 (7) 0,1,2
00001111 (15) 0,1,2,3
00011111 (31) 0,1,2,3,4
00111111 (63) 0,1,2,3,4,5
01111111 (127) 0,1,2,3,4,5,6

You usually leave the eighth processor alone because many system processes ”such as domain controllers ”default to that processor.

As an example, if you want to create the affinity for one SQL Server instance thread to use four processors of an eight-processor system, you could set this bit mask to be 15 (00001111). The result would be that SQL Server spawns its threads only on those processors, thus reducing overall reloading of the processor cache. This can be especially evident during heavy system loads.

 sp_configure 'affinity mask', 15  go RECONFIGURE Go 

In general, the default affinity value is able to provide ample load balancing across processors. Based on your particular processing load and application types, you will want to allocate CPUs accordingly . Below is a general recommendation of what to specify based on the different application types you are running:

  • OLTP: Use default value of .

  • Data warehouse: Potentially use 75 percent of available processors to maximize on the huge data loads, large reporting, and number of users.

  • OLAP: Use default value of .

  • Mixed: Use default value of .

From Enterprise Manager, SQL Server Properties, Processor tab, just select the targeted processors in the Processor Control section.

Allow Update

Type ”Basic

Default value ”

By default, SQL Server does not allow updates to internal system tables. When you set the allow update value to 1 , any user with proper permissions can update system tables. Sometimes you might want to allow access to system tables.

Following are a couple scenarios:

  • In many database applications, business logic is written in stored procedures and triggers. To protect your intellectual property, you might not want anyone to see and modify this logic. One way to accomplish this goal is to delete entries from the syscomments table for the objects you want to protect.

  • My database is currently running off a Jaz drive connected to my machine, and sometimes, my Jaz drive is disconnected. In such cases during startup, SQL Server fails to initialize the database file and marks the database corrupt by setting the status bit in the sysdatabases table to 256. To bring the database back to life, I connect my Jaz drive, change the status bit to the original value, and restart the machine. Then SQL Server recognizes the drive and starts the database normally.

CAUTION

Only highly experienced users of SQL Server should use the Allow Update option. For example, if you create a stored procedure that modifies system tables when the Allow Update option is turned on, the procedure will continue to be able to modify the system table even if you turn off allow update again. Therefore, be very careful when you set this parameter value to 1 .

Even more important is to turn off Allow Update as soon as you are finished with the task of modifying system tables.

From Enterprise Manager, SQL Server Properties, Server Settings tab, select the Allow Modifications box, as shown in Figure 40.5.

Figure 40.5. SQL Server Properties: Server Settings.

graphics/40fig05.jpg

AWE Enabled

Type ”Advanced

Default value ”

SQL Server can use the Advanced Windowing Extensions (AWE) API to support large amounts of physical memory ”in fact, as much as 8GB of memory on Windows 2000 Advanced Server and up to 64GB on Windows 2000 Data Center. The default of tells SQL Server to use dynamic memory in standard 32-bit virtual address spaces. By enabling AWE, the SQL Server instance does not dynamically manage the size of the address space. The instance holds all memory acquired at startup until it is shut down, and memory pages come from the Windows nonpageable pool. This means that none of the memory of the instance can be swapped out. You end up with a great deal of all activity occurring in memory only. This is potentially a fast database engine.

AWE enabled is usually used in conjunction with the max server memory option to control how much memory each SQL Server instance will use.

 sp_configure 'awe enabled', 1  go RECONFIGURE Go 

Setting of this option will vary according to the following application types:

  • OLTP: If memory is available, set to 1 .

  • Data warehouse: Not appropriate for this type.

  • OLAP: If memory is available and you are not using OLAP file options, set to 1 .

  • Mixed: If memory is available, set to 1 .

Cost Threshold for Parallelism

Type ”Advanced

Default value ” 5

SQL Server now supports parallel query execution. Before a query is executed, SQL Server's cost-based optimizer estimates the cost of execution for a serial plan, a plan that uses a single thread. The option to set the cost threshold for parallelism allows you to specify a threshold in seconds; if the cost of the serial execution plan (in seconds) is greater than the value specified by this parameter, SQL Server will consider a parallel query execution plan. A query will not become a candidate for parallel query execution simply based on this fact. Because parallel query execution is supported only on an SMP server, this value is ignored for non-SMP hardware. For an application that uses many complex queries, set this value to a lower number so that you can take advantage of the parallel query execution capabilities of SQL Server.

 sp_configure 'cost threshold for parallelism', 2  go RECONFIGURE Go 

Setting of this option will vary according to the following application types:

  • OLTP: Use default value of 5 .

  • Data warehouse: Many complex queries are candidates for parallelism. Set to low value, perhaps 2 (seconds).

  • OLAP: Use default value.

  • Mixed: Use default value.

Cursor Threshold

Type ”Advanced

Default value ” “1

This option allows you to specify when SQL Server should generate a cursor result set asynchronously. If the optimizer estimates that the number of rows returned by the cursor is greater than the value specified by this parameter, it will generate the result set asynchronously. The optimizer makes this decision based on the distribution statistics for each table that is participating in the join in the cursor.

To determine the optimal value for this parameter, make sure that statistics are up to date (by running update statistics ) for the tables used in the cursors. By default, SQL Server generates a cursor result set synchronously. If you are using a fair amount of cursors that return a large number of result sets, setting this value to a higher value will result in better performance. Setting this value to will force SQL Server to always generate a cursor result set asynchronously.

 sp_configure 'cursor threshold', 100000  go RECONFIGURE go 

Setting of this option will vary according to the following application types:

  • OLTP: Use default value.

  • Data warehouse: A data warehousing environment is the largest potential user of this option due to the high volume of result rows returned by applications using data warehouses. Setting this value to 100,000 is a good starting point.

  • OLAP: Use default value.

  • Mixed: Use default value.

Default Language

Type ”Basic

Default value ”

This option specifies the language ID currently in use by SQL Server. The default value is , which specifies the U.S. English system. As you add languages on the server, SQL Server assigns a new ID for each language. You can then use these IDs to specify the default language of your choice. You can add languages using the SQL Server setup program. Adding a language allows SQL Server to display error messages and date/time values in the format that is appropriate for that language. Set this option in the Server Settings tab of the SQL Server Properties dialog box.

Fill Factor

Type ”Basic, static

Default value ”

The Fill Factor option allows you to define the percentage of free space on a data page or an index page when you create an index or a table. The value can range from 1 “100. Setting the value to 80 would mean each page would be 80 percent full at the time of the create index . SQL Server also allows you to specify the value of fill factor at the server level by providing a fill factor parameter.

 sp_configure 'fill factor', 90  go RECONFIGURE Go 

Setting of this option will vary according to the following application types:

  • OLTP: This is a good candidate for leaving space free in pages due to the update, delete, and insert characteristics. Try 90 percent full value first and watch the page split activity.

  • Data warehouse: Use default value.

  • OLAP: Use default value.

  • Mixed: Use default value.

As you can see in Figure 40.6, you set fill factor from the Database Settings tab in the SQL Server Properties dialog box. Just click on the Fixed option and slide the bar until you have the desired fullness of a page.

Figure 40.6. SQL Server Properties: Database settings.

graphics/40fig06.jpg

Index Create Memory (KB)

Type ”Advanced, Self-configuring

Default value ”

The index create memory option is used to control the amount of memory used by index creation sorts. It is a self-configuring option and usually doesn't need to be adjusted. However, if you are having problems with the creation of large indexes, you might want to try specifying a KB value here that will contain the sort portion of the index create.

 sp_configure 'index create memory', 1000  go RECONFIGURE go 

Lightweight Pooling

Type ”Advanced

Default value ”

Lightweight pooling is relevant to SMP environments that are having excessive context switching. By flipping this switch, you might get better throughput by performing the context switching inline, thus helping to reduce user/kernel ring transitions. Lightweight pooling causes SQL Server to switch to fiber mode scheduling.

 sp_configure 'lightweight pooling', 1  go RECONFIGURE go 

Setting of this option will vary according to the following application types:

  • OLTP: This is a good candidate for usage if on an SMP environment.

  • Data warehouse: This has a good potential for usage if on an SMP environment.

  • OLAP: Use default value.

  • Mixed: Use default value.

Locks

Type ”Advanced, Self-configuring

Default value ”

In earlier versions of SQL Server, the DBA had to specify the number of locks available to SQL Server. If this parameter was set to a low value, a query requiring a large number of locks would fail at runtime. Setting it too high would result in wasting memory that otherwise could be used to cache data. SQL Server 2000 can handle locks dynamically if this parameter is set to the default value ( ). SQL Server initially allocates 2 percent of memory available to SQL Server. As lock resource structures are consumed, the lock manager allocates more lock resources to the pool to a maximum of 40 percent of the memory available on SQL Server. Unless you are certain of the overall lock consumption of your application, you probably don't need to change this value.

 sp_configure 'locks', 10000  go RECONFIGURE go 

Max Degree of Parallelism

Type ”Advanced

Default value ”

This option specifies the number of threads to be used for parallel query execution. On a non-SMP server, this value is always ignored. For an SMP server, a default value of signifies that all the CPUs will be used for parallel query execution. If you set this value to 1 , all query plans will be serialized. If the affinity mask option is on, parallel query execution will take place only on the CPUs for which the affinity mask bit is turned on. In that way, the two options can be used in conjunction. The application types assessment will be the same as described in the affinity mask option.

This option can be set up using the Processor tab of the SQL Server Configuration dialog box; then, in the Parallelism box, choose the number of processors to use for parallelism from the drop-down list box.

 sp_configure 'max degree of parallelism', 4  go RECONFIGURE go 

Max Server Memory and Min Server Memory

Type ”Advanced, Self-configuring

Default value ” 2147483647 and

Max server memory specifies the maximum amount of memory (in terms of MB) that is available to SQL Server. It is used in conjunction with min server memory and essentially establishes an upper and lower bound for memory allocation. SQL Server uses this memory for user connections, locks, internal data structures, and caching the data. This is the memory pool described earlier. The default value of 2147483647 for the Max Server Memory option means that SQL Server will perform dynamic allocation of memory from the operating system based on available physical memory on the machine. The default value of for the Min Server Memory option means that SQL Server will start allocation memory as it is needed, and then never go below the minimum value after it is reached.

The SQL Server lazywriter process is responsible for making sure that enough memory is available to SQL Server for the optimal number of buffers and Windows so that no excess paging occurs at the operating-system level. The lazywriter process frequently checks physical memory available on the machine. If the memory available is greater than 5MB, lazywriter assigns excess memory to the SQL Server buffer cache.

In addition, watch the Working Set performance counter that shows the amount of memory used by a process (SQL Server in this case). If this number is consistently below the amount of memory for which SQL Server is configured, then SQL Server is configured for more memory than it needs. You can also adjust the Set Working Set Size configuration option.

If SQL Server is the only application running on the machine, you might want to perform static memory allocation. Be careful when you allocate fixed memory to SQL Server. If you allocate more memory to SQL Server than the machine has, SQL Server will fail to start. Use the -f option during startup to bring up SQL Server with the default configuration. Change the value to the correct value, and restart SQL Server.

 sp_configure 'max server memory', 200  go RECONFIGURE Go sp_configure 'min server memory', 10 go RECONFIGURE go 

For a strict fixed allocation of memory for SQL Server, make the min and max values the desired allocation size the same (like 200MB). A fixed amount of memory will then be allocated for SQL Server.

Figure 40.7 shows two possible settings for these configuration options. One shows the Dynamic Configure option set and a minimum and maximum value are established (other than the defaults). The other one shows the fixed memory specification. This fixed memory setting will result in the minimum and maximum values being set to the same desired value.

Figure 40.7. SQL Server Properties: Memory settings.

graphics/40fig07.jpg

Setting of this option will vary according to the following application types:

  • OLTP: For those with heavy loads, this is a good candidate for high fixed memory settings.

  • Data warehouse: Use default values.

  • OLAP: Use default value.

  • Mixed: For those with heavy loads, this is a good candidate for high fixed memory settings.

Max Text Repl Size

Type ”Basic, dynamic

Default value ” 65536

This parameter specifies the maximum size of the text and image datatypes for columns that are participating in replication during single insert , update , writetext , and updatetext statements. You might need to raise this value if the image sizes with which your application deals are consistently large and the data is part of a replication configuration.

 sp_configure 'max text repl size', 131072  go RECONFIGURE go 

Max Worker Threads

Type ”Basic

Default value ” 255

SQL Server uses native operating system threads. This parameter specifies the maximum number of threads available for SQL Server processes. One or more threads are used for supporting each network protocol (such as TCP/IP and named pipes). SQL Server is configured to listen. The checkpoint and lazywriter processes also consume threads. A pool of threads is used to handle user connections. When the number of connections is lower than the max worker thread parameter value, a thread is created for each connection. When more connections are on the server than the value defined by the max worker thread parameter, SQL Server provides thread pooling for efficient resource utilization.

More threads can create overhead on the system processors. Therefore, lowering this value might sometimes improve the performance of the system. For a system with a few hundred user connections, a reasonable value for this parameter is 125. You might want to experiment with various values to determine the appropriate setting for this parameter. An SMP environment can easily handle more threads, and you can increase the number of threads accordingly.

 sp_configure 'max worker threads', 125  go RECONFIGURE go 

Figure 40.8 shows the setting of the Max Worker Threads option from the Processor tab of the SQL Server Properties dialog box. Choose a value between 10 1,024 for this option.

Figure 40.8. SQL Server Properties: Processor settings.

graphics/40fig08.jpg

Setting of this option will vary according to the following application types:

  • OLTP: For SMP environments, set the value upward because those environments can handle servicing more threads. This will yield performance gains.

  • Data warehouse: Use default value.

  • OLAP: Use default value.

  • Mixed: For SMP environments, set the value upward because those environments can handle servicing more threads. This will yield performance gains.

Min Memory Per Query

Type ”Advanced

Default value ” 1024 KB

Min memory per query specifies the minimum amount of memory that will be allocated for the execution of a query. Normally, the SQL Server query processor will attempt to determine the optimal amount of memory for a query. This option allows the sysadmin to specify this value instead. Increasing this value usually improves queries that handle hash and sort operations on a large volume of data. This option replaces the Sort Page option in SQL Server 7.0 and earlier.

 sp_configure 'min memory per query', 2048  go RECONFIGURE go 

Looking back at Figure 40.7, the Memory tab of the SQL Server Configuration dialog box, you can see the Minimum query memory value option set at 1024 (the default).

Setting of this option will vary according to the following application types:

  • OLTP: Use default value.

  • Data warehouse: This is a good opportunity to better service numerous canned queries in this environment. Set the value higher than the default.

  • OLAP: Use default value.

  • Mixed: Use default value.

Nested Triggers

Type ”Basic

Default value ” 1

As the name suggests, nested triggers specifies whether a trigger event on a table will fire another trigger. The nesting level of triggers is 32 , and it used to be 16 . If you reach this limit of 32 , SQL Server will give an error and roll back the transaction. The default value of 1 means that a trigger on a table can cause another trigger to fire.

Take a look again at Figure 40.5; you can see the Nested Trigger option checked under the Server Behavior box.

Network Packet Size

Type ”Basic

Default value ” 4096

This parameter specifies the default network packet size for SQL Server. Setting this value to a higher number (which should be divisible by 512) can improve the performance of applications that involve a large amount of data transfer from the server. Check your network configuration and set an appropriate value for this parameter. In this same regard, you can improve performance by lowering the size value for applications that are small in data transfer size. However, the usual scenario is to increase this size to accommodate large amounts of data transfer, as with Bulk Loads.

 sp_configure 'network packet size', 8192  go RECONFIGURE go 

TIP

You can also specify the network packet size from the client when you connect to SQL Server (using the -a option for isql , osql , and bcp ). Setting the network packet size from a client can be useful when the default network packet size is adequate for general application needs. However, a larger packet size might be needed for some specific operations, such as bulk copy. You can also call OLE DB, ODBC, and DB-Library functions to change the packet size.

Setting of this option will vary according to the following application types:

  • OLTP: Possibly decrease this size to 512 if all queries deal with small amounts of data transfer, which is often the case in OLTP or ATM applications.

  • Data warehouse: Perhaps increase this to 8192 to handle the consistently large data transfers in this environment.

  • OLAP: Use default value.

  • Mixed: Use default value.

Open Objects

Type ”Advanced, Self-configuring

Default value ”

This option is self-configuring by default (when 0 is the setting). SQL Server will increase or decrease the number of open object descriptors in memory based on the needs of the server. You will rarely have to change from the default.

 sp_configure 'open objects', 10000  go RECONFIGURE Go 

Priority Boost

Type ”Advanced

Default value ”

This option is used to specify the process priority of SQL Server processes on the Windows NT or Windows 2000 operating system. The default value of means that SQL Server should run on the same priority level ”a priority base of 7 ”as other applications on the machine. Priority boost can be turned on if you have plenty of horsepower to deal with all other services on the box, as in an SMP environment. When turning on priority boost, the priority base of SQL Server is elevated to 13 .

 sp_configure 'priority boost', 1  go RECONFIGURE go 

NOTE

Don't set the value of this parameter to 1 , except in the case of a dedicated SQL Server with SMP hardware.

From Figure 40.8, you can see the Boost SQL Server Priority on Windows option in the Processor control function. Use care when applying this option.

Query Governor Cost Limit

Type ”Advanced

Default value ”

Queries are often the cause of major performance problems. SQL Server can handle the queries, but many are poorly written and don't restrict the search criteria enough. This can result in runaway queries that are returning large result sets and can adversely affect the entire server's performance. A method to control this is to cut the query off at the pass by specifying a maximum cost limit to queries. If any query's cost is greater than this maximum value, the query is not allowed to execute. This value is server-wide and cannot be applied to just one query.

 sp_configure 'query governor cost limit', 300  go RECONFIGURE Go 

Query governor cost limit can be set by going to Server Behavior options, SQL Server Configuration dialog box, Server Settings tab.

Setting of this option will vary according to the following application types:

  • OLTP: Use default value.

  • Data warehouse: This is a must-have option for this environment. Try setting this value to 300 seconds, and then get ready for the users to scream at you. On the positive side, the server won't get bogged down or freeze again.

  • OLAP: For OLAP that use SQL Server storage, set this value to 600 seconds to get started, and then reduce it over time.

  • Mixed: Same protection opportunity here. This won't affect the OLTP queries, so it is safe to apply.

Query Wait

Type ”Advanced

Default value ” “1

Queries that are memory intensive and involve huge sorts might take a long time to execute based on the available memory during execution. SQL Server internally calculates the timeout interval for such queries. Usually, this is quite a large number. You can override this value by specifying a value (in seconds) using the query wait parameter of SQL Server. If you set this value too low, you risk more frequent query timeouts when your system is under a heavy load and a highly concurrent environment.

 sp_configure 'query wait', 20  go RECONFIGURE go 

Recovery Interval

Type ”Advanced, Self-configuring

Default value ”

Recovery interval is used to specify the maximum time (in minutes) that SQL Server would require to recover a database during startup. During startup, SQL Server rolls forward all the changes that were committed during a SQL Server crash and rolls back the changes that were not committed. Based on the value specified by this parameter, SQL Server determines when to issue a checkpoint in every database of SQL Server so that in the event of a crash, SQL Server can recover the databases in a time specified by recovery interval. If the value of the recovery interval parameter is low, SQL Server will issue checkpoints more frequently to allow a recovery to be faster; however, frequent checkpoints can slow down the performance. Setting recovery interval too high will create a longer recovery time for databases in the event of a crash. The default value of leaves this option open to SQL Server to determine the best value.

 sp_configure 'recovery interval', 10  go RECONFIGURE go 

Figure 40.6 shows the Recovery Interval option setting of within the Recovery section. Values must be between 1 and 32,767 .

Setting of this option will vary according to the following application types:

  • OLTP: Use default value.

  • Data warehouse: This is an opportunity to save on checkpoints and not degrade performance in this mostly read-only environment. Set this value high.

  • OLAP: Same performance opportunity here in this read-only environment.

  • Mixed: Use default value.

Remote Proc Trans

Type ”Basic

Default value ”

Remote proc trans allows remote procedures that are taking part in multiserver transactions to use MS-DTC so that transaction integrity is maintained across servers. The default value of means the remote procedure calls will not use MS-DTC. Data modification at the remote server will not be a part of transactions at the local server. If you set this parameter to 1 , SQL Server uses MS-DTC to preserve transaction integrity across servers.

 sp_configure 'remote proc trans', 1  go RECONFIGURE Go 

Figure 40.9 illustrates the Enforce Distributed Transactions (MTS) option being set in the Remote Server Connections section of the Connections tab. This will primarily be important in OLTP environments.

Figure 40.9. SQL Server Properties: Connection settings.

graphics/40fig09.jpg

Setting of this option will vary according to the following application types:

  • OLTP: If you are having to support distributed transactions, this option should be set to On.

  • Data warehouse: Use default value.

  • OLAP: Use default value.

  • Mixed: If you are having to support distributed transactions, this option should be set to On.

Scan for Startup Procs

Type ”Advanced

Default value ”

When this option is set to 1 , SQL Server will scan for and execute all automatically executed stored procedures on the server on startup. To set a stored procedure to become automatically executed, you use the sp_procoption system-stored procedure. Executing a stored procedure at startup time is typically done when you want to have certain processing occur that creates the proper working environment for all subsequent database processing on the server. Executing at startup can also be done when you want to make sure that certain stored procedures' execution plans (with proper optimizer decisions) are already in procedure cache before anyone else has requested their execution.

 sp_configure 'scan for startup procs', 1  go RECONFIGURE go 

Set Working Set Size

Type ”Advanced

Default value ”

When set working set size is set to 1 , SQL Server allocates and locks the requested server fixed memory amount at startup. Min server memory and max server memory options must contain the same value, reflecting a fixed memory size request. This effectively guarantees you the memory for as long as SQL Server is up and running and doesn't ever get pages swapped out when SQL Server is idle. This option has no effect when the memory option is set to ; SQL Server is configured for dynamic memory allocation.

 sp_configure 'set working set size', 1  go RECONFIGURE Go 

Looking at Figure 40.7, you can request that SQL server use a fixed memory size and also reserve physical memory for SQL Server. Set working set size is activated when you check this Reserve Physical Memory for SQL Server box. You cannot use this option when you are allowing SQL Server to dynamically configure memory.

Setting of this option will vary according to the following application types:

  • OLTP: For those with heavy loads, this is a good candidate for high fixed memory settings.

  • Data Warehouse: Use default values.

  • OLAP: Use default value.

  • Mixed: For those with heavy loads, this is a good candidate for high fixed memory settings.

Show Advanced Options

Type ”Advanced, dynamic

Default value ”

By default, you will not see the advanced configuration parameters of SQL Server. By setting show advanced options to 1 , you will be able to see all the SQL Server parameters that can be set by the sp_configure command.

User Connections

Type ”Advanced, Self-configuring

Default value ”

User connections specifies the number of concurrent users that are allowed on SQL Server. When the value is , SQL Server can configure the needed user connections dynamically as they are needed. If you specify a value, you will be limited to this maximum number of user connections until you specify a larger value. If you specify other than a value, the memory allocation for user connections will be allocated at SQL Server startup time and burn up portions of the memory pool. Each connection takes up 40KB of memory space. If you configure SQL Server for 100 connections, SQL Server will pre-allocate 4MB (40KBx100) for user connections. You can see that setting this value too high might eventually impact performance because the extra memory could have been used to cache data. In general, user connections are now best left to be self-configuring.

 sp_configure 'user connections', 200  go RECONFIGURE go 

Looking back at the SQL Server Properties dialog box and the Connections tab in Figure 40.9, you can set a value for the User Connections option by entering a value in the Maximum Concurrent User Connections box. This value must be between 5 and 32,767 .

User Options

Type ”Basic, static

Default value ”

User options 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 bit-mask field, and each bit represents a user option. Table 40.1 outlines the values that you can set with this parameter.

Table 40.1. Specifying User Options
Bit Mask 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 null in aggregate warnings.
16 ANSI_PADDING controls padding of fixed-length variables .
32 ANSI_NULLS controls null handling when using equality operators.
64 ARITHABORT terminates a query when an overflow or divide-by-zero error occurs during query execution.
128 ARITHIGNORE returns NULL when an overflow or divide-by-zero error occurs during a query.
256 QUOTED_IDENTIFIER differentiates between single and double quotation marks when evaluating an expression.
512 NOCOUNT turns off the message returned at the end of each statement that states how many rows were affected by the statement.
1024 ANSI_NULL_DFLT_ON alters the session's behavior to use ANSI compatibility for nullability. New columns that are defined without explicit nullability are defined to allow NULL s.
2048 ANSI_NULL_DFLT_OFF alters the session's behavior to not use ANSI compatibility for nullability. New columns defined without explicit nullability are defined not to allow NULL s.
4096 CONCAT_NULL_YIELDS_NULL will have SQL Server return a NULL when concatenating a NULL value with a string.
8192 NUMERIC_ROUNDABORT will have SQL Server generate an error if loss of precision ever occurs in an expression.
16384 XACT_ABORT will have SQL Server roll back a transaction if a Transact -SQL statment raises a runtime error.

For a given user connection, you can use the @@options global variable to see the values that have been set.

 sp_configure 'user options', 256  go RECONFIGURE Go 

As you can see from the SQL Server Configuration dialog box and the Connections tab in Figure 40.9, the Default connection options can be checked or unchecked according to what you want to be in effect server-wide for all user connections. Again, a user can override these with his own SET command during a session.



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net