sp_configure System Stored Procedure

For SQL Server 6.5 information, see sp_configure in What's New for SQL Server 6.5.

Displays or changes configuration options.

Syntax

sp_configure [config_name [, config_value]]

where

config_name

Specifies a configuration option. SQL Server understands any unique string that is part of the configuration name. This parameter must be enclosed in quotation marks if it contains embedded blanks or is a keyword.

config_value

Is the value for the configuration option.

Remarks

All users can execute the sp_configure system stored procedure with no parameters (SQL Server displays a list of all configuration options and their current values) or with config_name (SQL Server displays the current value for the specified configuration option).

Only the system administrator can execute sp_configure with both parameters and change the configuration option value. After using sp_configure, the system administrator must execute the RECONFIGURE statement to install the changed value. Dynamic options take effect immediately after the RECONFIGURE statement has been run. For all non-dynamic options, SQL Server must be stopped and restarted before the options can take effect. For details, see the RECONFIGURE statement.

The information returned by sp_configure, when executed with no parameters, shows a config_value column and a run_value column. The config_value column contains the value to which the configuration option was set with sp_configure (the value in sysconfigures.value). The run_value column contains the value for that option (the value in syscurconfigs.value) for SQL Server. These values do not necessarily have to be equivalent. For example, the system administrator may have changed an option with sp_configure but has not executed the RECONFIGURE statement (for dynamic options) or restarted SQL Server (for non-dynamic options).

The run_value column contains the value that SQL Server is using. This value changes for dynamic configuration options (listed in the Configuration Option Overview and marked with an asterisk) after the system administrator runs the RECONFIGURE statement. This value specifies changes for static configuration options after you shut down and restart SQL Server. This is the value in syscurconfigs.value.

Configuration Option Overview

Many configuration options are often considered advanced and should be changed only under very special and often rare circumstances. In the following table, advanced options are shown in bold type; dynamic options are marked with an asterisk (*).

Advanced options are displayed only when "show advanced option" is set to 1 (the default is 0).

Configuration Option Details

Many configuration options can be grouped and are often changed together.

In the following listings, advanced configuration options are noted in bold type and dynamic options are marked with an asterisk (*).

allow updates*

Specifies whether or not direct updates are allowed against system tables. Can be on (1) or off (0). By default, this option is off (0); system tables can be updated only through system procedures, not through ad hoc updates.

When allow updates is on (1), any user who has appropriate permissions can update the system tables directly with ad hoc updates and can create stored procedures that update the system tables.

Important Allowing direct updates to the system tables is risky. Updating certain fields in the system tables can prevent SQL Server from running. Stored procedures created while allow updates is on (1) will always be able to update the system tables, even after the option has been turned off.

Because the system tables are critical, it is best to turn on this option only in tightly controlled situations. To guarantee that no other users can access SQL Server while the system tables can be directly updated, restart SQL Server from the Win32 command prompt with the /m option. For more information, see the sqlservr Command-line Executable.

This command starts SQL Server in a single-user mode, which allows only one system administrator to log in and turns on the allow updates configuration option.

The allow updates option is a dynamic option, which means that a new value takes effect as soon as you use the RECONFIGURE WITH OVERRIDE statement. As an added protection, the WITH OVERRIDE clause is always required for this configuration option.

backup buffer size*

Specifies the size of the dump and load buffer (used to increase backup speed). This value determines the size, in 32-page increments, of the backup buffer. For example, a backup buffer size of 5 means that a buffer of 160 pages (5 * 32 pages) will be created. This option, when reconfigured, takes effect immediately.

backup threads

Specifies the number of threads to be reserved for striped dump and load operations. A value of 0 turns off striped dumps. This option does not take effect until the server is stopped and restarted.

cursor threshold*

Specifies the value where keyset generation occurs asynchronously. When cursors generate a keyset for a results set, the optimizer estimates the number of rows that will be returned by that results set. If the optimizer estimates that the number of returned rows is greater than this threshold, the cursor is generated asynchronously. Otherwise, the cursor is generated synchronously. When set to -1, all keysets are generated synchronously. If the cursor threshold is set to 0, all keysets will be generated asynchronously. Normally you would not want to set this value too low, as small results sets are faster to build synchronously.

The accuracy of the optimizer to determine an estimate for the number of rows in a keyset depends on the currency of the statistics for each of the tables in the cursor. For more information on updating table statistics, see the DBCC and the UPDATE STATISTICS statements.

database size

Sets the default number of megabytes allocated to each new user database. The default is 2 MB; however, the minimum allowable database size is 1 MB, which allows databases to exist on removable media such as floppy disks. A database size specified in a CREATE DATABASE statement takes precedence over the value set by this configuration option.

If most of the new databases on your SQL Server require more than 2 MB, you might want to increase this value. You also must increase it if your model database grows larger than 2 MB, because the CREATE DATABASE statement causes SQL Server to copy model when creating a new user database.

Note The model database is 1 MB by default.

default language

Specifies the ID of the language that is used by the server to display system messages, unless a user has chosen another language from those available on the server. The language us_english always has an ID of 0. Additional languages are assigned unique numbers when they are added.

default sortorder id

Specifies the number of the sort order currently installed as the default on the server.

Note Do not use the sp_configure system stored procedure to change sort orders. For more information about changing sort orders, see the Microsoft SQL Server Administrators Companion.

fill factor

Determines how full SQL Server makes each page when it is creating a new index on existing data (unless the user specifies some other value using the CREATE INDEX statement). The fill factor percentage affects performance because SQL Server must take time to split pages when they fill up.

The fill factor percentage is used only at the time the index is created and becomes less important as changes to the data are made. The pages are not maintained at any particular level of fullness.

The default for this configuration option is 0; legal values range from 0 through 100. A fill factor of 0 does not mean that pages are 0 percent full. It is treated similarly to a fill factor of 100 in that SQL Server creates clustered indexes with full data pages and nonclustered indexes with full leaf pages. It is different from 100 in that SQL Server leaves a space within the index B-tree. There is seldom a reason to change the default fill factor, especially since you can override it with the CREATE INDEX statement.

If fill factor is set to 100, SQL Server creates both clustered and nonclustered indexes with each page 100 percent full. A fill factor of 100 makes sense only for read-only tables ¾ tables to which no additional data will ever be added.

Smaller fill factor values cause SQL Server to create new indexes with pages that are not full. For example, a fill factor of 10 is a reasonable choice if you are creating an index on a table that you know contains only a small portion of the data that it will eventually hold. Smaller fill factor values cause each index to take more storage space.

free buffers*

Determines the threshold of free buffers available to the system. The minimum value is 20 and the maximum value is equal to one-half the number of buffers available when the server is started. The lazy writer process ensures that the number of free buffers available to the system does not fall below this threshold.

This option is automatically changed by the system whenever the memory option is changed; free buffers will be equal to 5 percent of the available memory. After the memory option is changed, a message will be displayed describing the change to free buffers. After this change, free buffers can be manually reconfigured to any legal value.

hash buckets

Sets the number of buckets used for hashing pages to buffers in memory. If the value specified is not a prime number, the closest prime number is used. For example, specifying 8000 creates 7993 hash buckets (the default). On systems with a large amount of memory, this value can be increased to allow faster access to data residing in data cache. For systems with 160 MB or less, 7993 is an appropriate value. This option does not take effect until the server is stopped and restarted.

language in cache

Indicates the maximum number of languages that can be simultaneously held in the language cache. The default is 3.

Lock Escalation Options

Many configuration options can be grouped and are often changed together.

When queries request a large number of rows from a particular table or tables, SQL Server generates page-level locks. If a query requests a large percentage of the rows from the table (see LE threshold percent), then lock escalation will occur. This makes table scans and operations against a large results set more efficient. The lock escalation options apply per statement, and not per transaction. The lock escalation options are:

• LE threshold maximum*
• LE threshold minimum*
• LE threshold percent*

LE threshold maximum*

Determines the maximum number of page locks to hold before escalating to a table lock. If the number of page locks is greater than the escalation maximum, a table lock will occur regardless of whether or not the LE threshold percent has been exceeded. The default is 200.

LE threshold minimum*

Determines the minimum number of page locks required before escalating to a table lock. A table lock will occur only if this minimum is reached when the LE threshold percent is exceeded. LE threshold minimum prevents the server from escalating to a table lock for small tables where the LE threshold percentage is reached quickly. The default is 20.

LE threshold percent*

Specifies the percentage of page locks needed on a table before a table lock is requested. The default (0) causes a table lock to occur only when the LE threshold maximum has been reached.

Note To override these values for a given request, see the optimizer_hints option with the SELECT statement. The HOLDLOCK table lock options include UPDLOCK, TABLOCK, TABLOCKX, and PAGLOCK.

locks

Sets the number of available locks. Locks are not shared the way open databases and database objects are shared. The default is 5000.

Increase this value if SQL Server displays a message saying that you have exceeded the number of available locks. Since each lock consumes memory (32 bytes per lock), increasing this value can make it necessary to increase the amount of memory dedicated to the server.

logwrite sleep (ms)*

Specifies the number of milliseconds that a write to the log will be delayed if the buffer is not full. This increases the chance that more data will be added to the log buffer by other users, so that fewer physical log writes will be needed. Acceptable values for this option are -1 through 500. The special value of -1 means that the log write will not be delayed. The default is 0, which causes the server to wait only if other users are ready to execute.

max async IO

Configures the number of asynchronous I/Os that can be issued. The default is 8. This value should be changed only on systems with databases defined on multiple physical database devices that reside on separate physical disks, or on systems taking advantage of disk striping.

max lazywrite IO*

Tunes the priority of batched asynchronous I/Os performed by the lazy writer. This is comparable to max async IO, which controls batch I/O such as bulk copy and checkpoints, but max lazywrite IO is specific to the lazy writer. This option should be configured only on systems that have multiple hard disks. It is dynamically configurable up to the value specified by max async IO. Do not change this option unless your primary support provider instructs you to do so.

max worker threads*

Configures the number of worker threads that are available to SQL Server processes. SQL Server makes use of the native thread services of the operating system. Instead of one worker thread, there are many. Each network that SQL Server simultaneously supports is supported by one or more threads, another thread handles database checkpoints, and a pool of threads handles all users.

The max worker threads option allows you to control the number of threads allocated to the user pool. When the number of user connections is less than max worker threads, one thread handles each connection. However, if the number of connections exceeds max worker threads, thread pooling occurs. Additionally, if the configured value for worker threads is exceeded, the request is handled by the next worker thread that completes its current task. The default is 255.

media retention

Sets the number of days that you expect to retain each backup medium after it has been used for a database or transaction log dump. The default is 0. Unless you change this value, no warning is issued. A typical value might be 7 (days).

If you try to use the backup medium before the set number of days have passed, SQL Server issues a warning message.

memory

Sets the size of available memory, in 2K units. The default run value is determined by the setup program based on the amount of memory in your computer.

To optimize this number for your system, subtract the memory required for Windows NT (and other system uses, if the computer is not wholly dedicated to SQL Server) from the total physical memory.

Ideally, you want to allocate as much memory as possible to SQL Server without causing the system to page. You can use the Windows NT Performance Monitor to help determine what the threshold is for your system ¾ the Page Faults/sec counter of the Memory Object indicates whether you are generating any page faults. If so, SQL Server is running with too much memory. The threshold varies depending on your system. For example, on a 32-MB system, 16 MB might be appropriate for SQL Server; on a 64-MB system, 48 MB might be appropriate.

The amount of memory specified must be sufficient for the SQL Server static memory needs (kernel overhead, user stack space, and so on), as well as for the procedure cache and the data cache (also called buffer cache).

The memory left over after the SQL Server memory needs are met is divided between the procedure cache and the data cache. The percentage allocated to the procedure cache is set with the procedure cache configuration option.

You can use DBCC MEMUSAGE and statistics from the Windows NT Performance Monitor to help you adjust this value. Change this value only when you add or remove memory, or when you change how you use your system.

When the memory option is reconfigured, it will automatically cause a possible change to the configured free buffers configuration option. The free buffers configuration value will be set to 5 percent of the new memory size.

The maximum value for the memory option is 2 GB. Note, however, that the memory option does not include memory needs for tempdb if you have placed tempdb in RAM using the tempdb in ram (MB) option.

nested triggers*

Controls the use of nested triggers. When this option is 0, triggers will not cascade. Set it to 1 to enable nested (or cascading) triggers. The default is 1.

network packet size*

Sets the server-wide value for the default network packet size. The client application can override this value. On systems using differing network protocols, this option should be set to the size of the most common protocol used. This option can improve network performance when network protocols support larger packets. If reconfigured, the change takes effect immediately. The default is 4096.

open databases

Sets the maximum number of databases that can be open at one time on SQL Server. The default is 20.

open objects

Sets the maximum number of database objects that can be open at one time on SQL Server. The default is 500.

Increase this value if SQL Server displays a message saying that you have exceeded the number of open objects. Because open objects consume memory, increasing this value can make it necessary to increase the amount of memory dedicated to the server.

priority boost

Determines whether or not SQL Server should run at a higher priority than other processes on the same computer. If this option is set to 1, SQL Server will run at a higher priority in the Windows NT scheduler. The default is 0 and should be changed only on Windows NT systems dedicated to SQL Server.

procedure cache

Specifies the percentage of memory allocated to the procedure cache after the SQL Server memory needs are met. The SQL Server memory needs are the sum of memory necessary for locks, user connections, the code itself, and so on. The remaining memory is divided between the procedure cache and the data cache according to the percentage set by this configuration option.

The procedure cache is the area of memory where the most recently used procedures are stored. The procedure cache is also used when a procedure is being created and when a query is being compiled. If SQL Server finds a procedure or a compilation already in the cache, SQL Server does not need to read it from the disk.

The data cache is the area of memory where the most recently used data pages and index pages are stored. If SQL Server finds a data page or index page that has already been called by a user in the cache, SQL Server does not need to read it from the disk.

Information in both caches is stored in least recently used, most recently used (LRU-MRU) fashion.

The default for the procedure cache configuration option is 30, which gives the procedure cache 30 percent of the remaining memory after the SQL Server requirements are met. By default, the data cache gets the other 70 percent.

Since the optimum value for this configuration option is different from application to application, resetting it can improve SQL Server performance. For example, if you run many different procedures or ad hoc queries, your application will use the procedure cache more, so you might want to increase this value. Many applications fall into this category while they are being developed. You might want to set this option to 50 during your development cycle and then reset it to 30 when your application has stabilized.

You can use DBCC MEMUSAGE to help you adjust this value.

Read Ahead Configuration Options

Many configuration options can be grouped and are often changed together.

Read ahead configuration options control "parallel data scan" capabilities, which enable asynchronous read ahead of data when SQL Server determines that pages are being retrieved in sequential order. With read ahead (RA), separate background threads will be used to pre-fetch pages for a given results set. Read ahead is used by queries, DBCC CHECKTABLE, DBCC CHECKDB, DBCC CHECKALLOC, UPDATE STATISTICS, CREATE INDEX, exporting with bulk copy, and the retrieval of text and image data. Read ahead pre-fetches are configured based on extents. The Read Ahead configuration options are:

• RA cache hit limit*
• RA cache miss limit*
• RA delay*
• RA pre-fetches*
• RA slots per thread
• RA worker threads

RA cache hit limit*

Is the number of cache hits that a read-ahead request can have before it is canceled. This is used for detecting situations in which the Read Ahead manager finds everything in cache and is of little help to the query. The default value '4' should suffice for most systems. Do not change this option unless your primary support provider instructs you to do so.

RA cache miss limit*

Is the number of cache misses that occur during a horizontal traversal before read ahead starts for that command. Avoid setting this value lower than 3. Setting the RA cache miss limit to 1 causes a read-ahead request to be made whenever a data page is accessed from disk, and it can lead to thrashing and poor performance. Do not change this option unless your primary support provider instructs you to do so.

RA delay*

Specifies the delay of read ahead, in milliseconds. This is required to account for the latency to "prime" the RA Manager before it starts servicing an initial RA request. The default value '15' should suffice for most systems. For SMP machines, this value should be set to 15.

RA pre-fetches*

Determines how far ahead the read-ahead (RA) manager will read (on an extent basis) before the pre-fetch manager idles. A value of 3 means that for each request posted, the RA manager keeps three extents ahead of the current scan position.

RA slots per thread

Is the number of simultaneous requests each read-ahead (RA) service thread will manage. The number of threads multiplied by the number of slots is equivalent to the total number of concurrent RA scans that the system will support. The default value should be sufficient for most systems. If your system has an efficient IO subsystem, you may be able to increase the number of scans that a single thread can handle.

RA worker threads

Specifies the number of threads used to service read-ahead requests. Each thread will manage a configurable number of structures (see the RA slots per thread option), where each of these structures (slots) represents an individual range scan. This option should be set to the maximum number of concurrent users on the system. A warning will be logged in the error log if the number of threads requesting RA scans exceeds the number of configured RA slots.

recovery flags

Determines what information SQL Server displays in the error log during recovery. Can be 0 or 1. The default is 0, which means that SQL Server writes only the database name and a message stating that recovery is in progress. 1 means that SQL Server displays information about each individual transaction, including whether it was canceled or committed.

recovery interval*

Sets the maximum number of minutes per database that SQL Server needs to complete its recovery procedures in case of a system failure. The default is 5 minutes per database.

SQL Server uses this number and the amount of activity on each database to decide when to do a checkpoint on each database. When SQL Server does a checkpoint, it writes all dirty pages (data pages that have been changed by data modification statements) to the disk. The checkpoint also performs a few other housekeeping tasks, including truncating the transaction log if this option has been set using the sp_dboption system stored procedure. A typical checkpoint takes about 1 second.

You might want to change the recovery interval as your application and its use change. For example, to guarantee that changes are frequently written to the disk, you can shorten the recovery interval when there is a lot of update activity. Shortening the recovery interval causes more frequent checkpoints, which slows the system slightly. On the other hand, setting the recovery interval too high might cause the recovery time to be unacceptably long.

remote access

Controls logins from remote SQL Servers. Can be 0 or 1. The default is 1. Set remote access to 0 to discontinue allowing remote access to or from a server.

remote login timeout*

Specifies the number of seconds to wait before returning from a remote login attempt. A value of 0 will allow an infinite wait.

remote query timeout*

Specifies the number of seconds to wait before returning from processing a remote query. A value of 0 will allow an infinite wait.

resource timeout*

Specifies the number of seconds to wait for a resource to be released. The default is 10. Increase this value if the SQL Server error log shows a lot of "logwrite" or "bufwait" timeout warnings.

set working set size

Directs Windows NT to reserve physical memory space for SQL Server equal to the sum of the memory setting and the size of tempdb if it is in RAM.

show advanced option*

Determines whether or not the advanced options should be displayed by the sp_configure system stored procedure. When set to 1, the advanced options are displayed. The default is 0.

SMP concurrency

Controls the number of threads that SQL Server will release to Windows NT for execution which, in effect, limits the number of CPUs used by SQL Server. On a uniprocessor computer, the optimal value is 1. On a symmetric multiprocessor (SMP) computer, the limit depends on whether or not the server is a dedicated SQL Server. If the server is not dedicated, reconfiguring this value can cause poor response time to other applications running on the same machine. If response time for other applications is not an issue, set SMP concurrency to -1, "Dedicated SMP Support," which means that there is no limit.

When SQL Server is installed, SMP concurrency will be set to 0, which means auto configure. In auto configure mode, the limit is set to N-1, where N is the number of processors detected at SQL Server startup. On a uni-processor machine this value will be set to 1. If "Dedicated SMP Support" is chosen, then SMP concurrency will be set to -1.

sort pages*

Specifies the maximum number of pages that will be allocated to sorting per user. On systems that perform large sorts, increasing this number can improve performance. Since additional sort pages will consume memory, increasing this value can make it necessary to increase the amount of memory dedicated to the server.

spin counter*

Specifies the maximum number of attempts a process will perform to obtain a resource. By default, SQL Server will attempt to set this value to 10000. This is the default for multiprocessor machines. On uniprocessor machines, the run value will default to 10.

tempdb in ram (MB)

Accepts a value for the size of the tempdb database, in megabytes. Zero causes tempdb to reside on a disk device; the default is the MASTER device. A non-zero value is the size of the tempdb in RAM. Regardless of whether it exists on disk or in RAM, tempdb is altered in the same way as any other database; however, when tempdb resides in RAM, it can be altered only 10 times without requiring the server to be shut down and restarted.

Altering tempdb while it is in RAM causes each alteration of the database to allocate a new "chunk" of contiguous memory to tempdb. This chunk of memory, although it is contiguous, is not necessarily located next to the existing portion(s) of tempdb in RAM. In order to obtain maximum performance, the server should be stopped and restarted after tempdb is altered.

Important If tempdb is in RAM at the time of an upgrade, it will subsequently be moved out of RAM. The default disk device(s) must have the minimum amount free (2 MB) to create tempdb. If not, SQL Server startup will fail. To temporarily force a 2-MB tempdb in RAM, use the -f flag on the command line with SQLSERVR.EXE. This will allow the server to start. Once it has started, you must perform one of the following options immediately:

• Increase the size of a default device
• Free space on a default device
• Add a default device

time slice

Sets the number of times that a user process is allowed to pass through a yield point without voluntarily yielding. If the time slice is set too low, SQL Server can spend too much time switching processes. If it is set too high, users can experience long response time.

The default is 100. There is seldom reason to change it.

user connections

Sets the maximum number of simultaneous connections to SQL Server allowed. The actual number of possible connections might be less than this value, depending on your database environment.

The number of user connections allowed depends on your version. For SQL Workstation, the number is 15, and for SQL Server the number is 32,767. However, the actual number is based on practical limits that vary depending on your application and hardware.

Use this statement to get a report on the maximum number of user connections that your system can use:

select @@max_connections

The memory overhead per connection is about 40K.

There is no formula for determining how many connections to allow for each user. Rather, you must estimate this number based on system and user requirements. Users executing DB-Library applications need one connection for each process started with a call to dbopen. On a system with many users, there is more likelihood that connections needed only occasionally can be shared among users.

Permission

Execute permission on sp_configure with no parameters or with only the first parameter defaults to all users. Execute permission for sp_configure with both parameters, used to change a configuration option, defaults to the system administrator. RECONFIGURE permission defaults to the system administrator and is not transferable.

Tables Used

master.dbo, master.dbo.syslanguages, sysconfigures, syscurconfigs, spt_values

Examples

A.    List the Advanced Configuration Options

This example shows how to set and list all configuration options. Advanced configuration options are displayed by first setting the show advanced option to 1. After this has been changed, executing sp_configure with no parameters will display all configuration options.

sp_configure 'show advanced option', 1
go
    Configuration option changed. Run the RECONFIGURE command to install.
RECONFIGURE
go
sp_configure

B.    Change a Configuration Option

This example sets the system recovery interval to 3 minutes.

sp_configure 'recovery interval', 3

See Also