For SQL Server 6.5 information, see sp_configure in What's New for SQL Server 6.5.
Displays or changes configuration options.
sp_configure [config_name [, config_value]]
where
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.
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 (*).
| config_name | Minimum | Maximum | config_value | run_value |
|---|---|---|---|---|
| allow updates* | 0 | 1 | 0 | 0 |
| backup buffer size* | 1 | 10 | 1 | 1 |
| backup threads | 0 | 32 | 5 | 5 |
| cursor threshold* | -1 | 2147483647 | 100 | 100 |
| database size | 1 | 10000 | 2 | 2 |
| default language | 0 | 9999 | 0 | 0 |
| default sortorder id | 0 | 255 | 52 | 52 |
| fill factor | 0 | 100 | 0 | 0 |
| free buffers* | 20 | 524288 | 204 | 204 |
| hash buckets | 4999 | 265003 | 7993 | 7993 |
| language in cache | 3 | 100 | 3 | 3 |
| LE threshold maximum* | 2 | 500000 | 200 | 200 |
| LE threshold minimum* | 2 | 500000 | 20 | 20 |
| LE threshold percent* | 1 | 100 | 0 | 0 |
| locks | 5000 | 2147483647 | 5000 | 5000 |
| logwrite sleep (ms)* | -1 | 500 | 0 | 0 |
| max async IO | 1 | 255 | 8 | 8 |
| max lazywrite IO* | 1 | 255 | 8 | 8 |
| max worker threads* | 10 | 1024 | 255 | 255 |
| media retention | 0 | 365 | 0 | 0 |
| memory | 1000 | 1048576 | 4096 | 4096 |
| nested triggers* | 0 | 1 | 1 | 1 |
| network packet size* | 512 | 32767 | 4096 | 4096 |
| open databases | 5 | 32767 | 20 | 20 |
| open objects | 100 | 2147483647 | 500 | 500 |
| priority boost | 0 | 1 | 0 | 0 |
| procedure cache | 1 | 99 | 30 | 30 |
| RA cache hit limit* | 1 | 255 | 4 | 4 |
| RA cache miss limit* | 1 | 255 | 3 | 3 |
| RA delay* | 0 | 500 | 15 | 15 |
| RA pre-fetches* | 1 | 1000 | 3 | 3 |
| RA slots per thread | 1 | 255 | 5 | 5 |
| RA worker threads | 0 | 255 | 3 | 3 |
| recovery flags | 0 | 1 | 0 | 0 |
| recovery interval* | 1 | 32767 | 5 | 5 |
| remote access | 0 | 1 | 1 | 1 |
| remote login timeout* | 0 | 2147483647 | 5 | 5 |
| remote query timeout* | 0 | 2147483647 | 0 | 0 |
| resource timeout* | -1 | 2147483647 | 100 | 100 |
| set working set size | 0 | 1 | 0 | 0 |
| show advanced option* | 0 | 1 | 1 | 1 |
| SMP concurrency | -1 | 64 | 0 | 1 |
| sort pages* | 64 | 511 | 64 | 64 |
| spin counter* | 1 | 2147483647 | 10000 | 10 |
| tempdb in ram (MB) | 0 | 2044 | 0 | 0 |
| user connections | 5 | 32767 | 20 | 20 |
Advanced options are displayed only when "show advanced option" is set to 1 (the default is 0).
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 (*).
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.
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.
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.
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.
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.
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.
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:
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.
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.
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.
If you try to use the backup medium before the set number of days have passed, SQL Server issues a warning message.
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.
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.
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.
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:
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.
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.
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:
The default is 100. There is seldom reason to change it.
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.
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.
master.dbo, master.dbo.syslanguages, sysconfigures, syscurconfigs, spt_values
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
This example sets the system recovery interval to 3 minutes.
sp_configure 'recovery interval', 3
| RECONFIGURE | sp_defaultlanguage |
| sp_dboption | sp_droplanguage |