Displays or changes global configuration settings for the current server.
The sp_configure stored procedure offers these options for the config_name parameter: affinity mask, max text repl size, remote conn timeout, remote proc trans, and user options.
For additional syntax information for sp_configure, see the Microsoft SQL Server Transact-SQL Reference.
sp_configure ['config_name'[, config_value]]
where
Note If a connection is involved in an MS DTC-coordinated distributed transaction, the connection will not be timed out even if its inactivity period has expired.
For information about other config_name options for sp_configure, see the Microsoft SQL Server Transact-SQL Reference.
| config_name | Minimum | Maximum | config_value | run_value |
|---|---|---|---|---|
| affinity mask | 0 | 2147483647 | 0 | 0 |
| max text repl size | 0 | 2147483647 | 65536 | 65536 |
| remote conn timeout | -1 | 32767 | 10 | 10 |
| remote proc trans | 0 | 1 | 0 | 0 |
| user options | 0 | 4095 | 0 | 0 |
user options is one or a combination of the following.
| Configuration value |
Description |
|---|---|
| 1 | DISABLE_DEF_CNST_CHK. Controls interim constraint checking. |
| 2 | IMPLICIT_TRANSACTIONS. Controls whether a transaction is started implicitly when a statement is executed. |
| 4 | CURSOR_CLOSE_ON_COMMIT. Controls behavior of cursors once a commit has been performed. |
| 8 | ANSI_WARNINGS. Controls truncation and NULL in aggregate warnings. |
| 16 | ANSI_PADDING. Controls padding of variables. |
| 32 | ANSI_NULLS. Controls NULL handling by 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 defined without explicit nullability will be defined to allow NULLs. |
| 2048 | ANSI_NULL_DFLT_OFF. Alters the session's behavior to not use ANSI compatibility for nullability. New columns defined without explicit nullability will be defined not to allow NULLS. |
Note Not all configuration values for user options are compatible. For example, ANSI_NULL_DFLT_ON cannot be enabled when ANSI_NULL_DFLT_OFF is enabled.
The bit positions in this option match those in @@OPTIONS. Each connection has its own @@OPTIONS variable. When logging into SQL Server, a user receives a default environment that specifies the @@OPTIONS value equal to the config_value. SET statements for several options will affect the value in @@OPTIONS.
For information about bit positions, see @@OPTIONS Global Variable earlier in this document.
Generally, Windows NT does not guarantee that any thread in a process will run on a given processor. A thread may migrate from processor to processor, which causes reloading of the the processor's cache. Under heavy system loads, specifying which processor should run which thread can improve performance by reducing the reloading of processor cache. The association between a processor and a thread is called processor affinity.
Processor affinity is determined by using an affinity mask. An affinity mask is a bit mask in which each bit represents a processor. By setting a bit that represents a processor to 1, that processor is selected for thread assignment. An affinity mask of 0, the default, specifies that SQL Server should not set the thread's affinity and instead should allow normal Windows NT scheduling algorithms to apply. A non-zero value is interpreted as a bit mask that specifies the processors eligible for selection.
By using the affinity mask option, symmetric multiprocessor (SMP) systems with more than four microprocessors operating under heavy load offer the best prospects for a performance increase. The affinity mask option can exclude SQL Server activity from processors given specific workload assignments by the Windows NT operating system. Excluding SQL Server threads from running on particular processors helps evaluate the system's handling of NT-specific processes. For example, to evaluate whether an additional network interface card (NIC) increases performance or to assess NIC performance with increasing loads, use the affinity mask option.
Before applying the affinity mask option to a computer system, however, consider that Windows NT assigns all I/O handling to processor 0, the first processor in the system. Changes in disk I/O, for example, will be reflected in the usage level of processor 0. Windows NT assigns delayed process call (DPC) activity associated with NICs to the highest numbered processor in the system. In systems with more than one installed and active NIC, each additional card's activity is assigned to the next higher numbered processor. For example, in an eight-processor system with one NIC, Windows NT assigns I/O to processor 0 and DPCs to processor 7. The same eight-processor system with two NICs will have I/O assigned to processor 0 and DPCs for each NIC assigned to processor 7 and to processor 6.
Note Use either the WPERF.EXE utility included in the Windows NT Resource Kit or the SQL Server Performance Monitor to view and analyze individual processor usage.
For example, if processors 1, 2, and 5 are selected as available with bits 1, 2, and 5 set to 1, a hexadecimal value of 0x13 or the decimal equivalent of 19 is specified. Number the bits from the right to left. The right-most bit is bit 1. Set bits 1, 2, and 5 (the first, second, and fifth bits) to 1. The number calculated from setting the specified bits is binary 00010011, which is decimal 19 or hexadecimal 0x13.
These are affinity mask options for an eight-processor system.
| Decimal value |
Binary bit mask |
Allow SQL Server threads on processor(s) |
|---|---|---|
| 1 | 00000001 | 0 |
| 3 | 00000011 | 0 and 1 |
| 7 | 00000111 | 0, 1, and 2 |
| 15 | 00001111 | 0, 1, 2, and 3 |
| 31 | 00011111 | 0, 1, 2, 3, and 4 |
| 63 | 00111111 | 0, 1, 2, 3, 4, and 5 |
| 126 | 01111110 | 1, 2, 3, 4, 5, and 6 (isolates SQL Server activity from I/O and DPC processors) |
| 127 | 01111111 | 0, 1, 2, 3, 4, 5, and 6 (isolates SQL Server activity from DPC processor only) |
| 254 | 11111110 | 1, 2, 3, 4, 5, 6, 7 (isolates SQL Server activity from I/O processor only) |
Because using SQL Server processor affinity is a specialized operation, it is recommended that SQL Server processor affinity be used only when necessary. In most cases, the Windows NT default affinity provides the best performance. Unlike earlier releases of SQL Server, if a config_value is 0, defaults will not be automatically reapplied.
Important After using sp_configure, the system administrator must execute the RECONFIGURE statement to install the changed value.
This example enables ANSI warnings (ANSI_WARNINGS) and implicit transaction (IMPLICIT TRANSACTIONS) options and disables all the others.
sp_configure 'user options', 10
This example defines a timeout period of 90 minutes for server-to-server connections.
sp_configure 'remote conn timeout', 90
This example sets processors 1, 2, 3, and 4 as available for threads. Each bit in the decimal represents a processor, so 15 = 0xf =1111(binary) meaning processors 1, 2, 3, and 4. You can also pass 0xf as the affinity mask argument.
sp_configure 'affinity mask', 15
This example sets all bits necessary for ANSI-level compatibility.
sp_configure 'user options', 1406
All users in the users group can execute this procedure with no parameters, or with only one parameter, to obtain the current parameter value(s). Only system administrators can use this stored procedure with two parameters.