SQL Server Configuration Options

The following SQL Server configuration parameters impact performance or performance-related resources. Each configuration parameter is defined with respect to its function and its impact on performance.

Memory—On computers with 32 MB or less, you should generally allocate at least 8 MB to 16 MB to Windows NT and configure SQL Server to use the rest. For computers with more than 32 MB, allocate 16 MB to 20 MB to Windows NT and allocate the rest of the memory to SQL Server.
Performance impact—Physical memory is used by SQL Server for server operation overhead, data (buffer) cache, and procedure cache. Therefore, to reduce SQL Server page faults, an appropriate amount of memory should be configured.
Max async IO—The max async IO parameter controls the number of outstanding asynchronous batch writes performed by checkpoint and lazy writer. The default is eight, and this is likely to be sufficient in most cases. However, you may want to experiment with this number to try to improve performance. Increasing the parameter allows more asynchronous batch writes to be performed, effectively shortening the period that the system is checkpointing or doing lazy writing. However, if your I/O subsystem cannot sustain the increased write activity, the increased writes will flood the I/O systems and can interfere with the ability of SQL Server or other processes to read from the disk, resulting in decreased throughput. The behavior of this parameter is therefore dependent to a large degree on the underlying I/O subsystem.
Performance impact—SQL Server for Windows NT uses the asynchronous I/O capability of the Windows NT operating system, which includes the Win32 API calls ReadFile(), ReadFileEx(), WriteFile(), and WriteFileEx(). For more information, see the Win32 SDK. Asynchronous, or overlapped I/O, refers to the ability of a calling program to issue an I/O request without waiting for completion to continue with another activity. When the I/O finishes, the operating system will notify the program by using a callback or other Win32 synchronization mechanism.

This has two main advantages. First, it makes implementation easier for an application designer, since the operating system can be used to perform asynchronous I/O rather than having to simulate this capability in the application. Second, the multiple outstanding I/O requests can drive certain high performance disk subsystems at greater performance levels than would be otherwise possible. This is generally only possible with very high-performance, intelligent, disk subsystems because only these types of systems have the specific features necessary to rapidly accept multiple asynchronous I/O requests from a Win32 application such as SQL Server.

On these systems, increasing the max async IO parameter of SQL Server can result in performance improvements during very disk-intensive operations. The actual setting used for this parameter and the resultant performance increase will vary depending on the exact hardware and database I/O profile. It should not be set arbitrarily high, since inordinate asynchronous I/O consumes system resources.
Working set—You can set 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. The values are 0 (no working set, the default) and 1 (working set).
The set working set size parameter (set with sp_configure or SQL Enterprise Manager) can disable the locking of memory as a working set. If "out of memory" errors occur, you may have too much memory assigned to SQL Server.
Sort pages—Specifies the maximum number of pages that will be allocated to sorting per user. The default is 64. On systems that perform large sorts, increasing this number can improve performance. Since additional sort pages consume memory, increasing this value can make it necessary to increase the amount of memory dedicated to the server. The minimum value is 50 and the maximum value is 511.
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 8,000 creates 7,993 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, 7,993 is an appropriate value and the default. This option does not take effect until the server is stopped and restarted. The minimum value is 4,999 and the maximum value is 26,500.
Procedure cache—By default, 30 percent of the available memory is reserved for the procedure cache. In systems with large amounts of memory, this is often excessive.
Performance impact—Having a properly-sized procedure cache results in fewer page faults with respect to use of stored procedures, triggers, rules, and defaults.
User connections—Sets the maximum number of simultaneous connections to SQL Server. The actual number of connections may be less depending on your database environment. The maximum value for this parameter is 32,767. However, this value must be set based upon available memory and application requirements.
Performance impact—Each user connection requires 40K of memory. Therefore, increasing user connections will increase the amount of memory needed for SQL Server overhead, thereby reducing the memory available for the data (buffer) and procedure caches. Worker threads is another fixed cost. Each allocated thread deducts from the memory available for procedure and data cache. Consequently, more physical memory may be required to maintain cache performance levels.
Open databases—This value sets the maximum number of databases that can be open at one time on SQL Server. The default is 20; this value can be set as high as 100.
Performance impact—Each open database resource consumes 650 bytes of memory. Therefore, increasing the number of open databases increases the amount of memory needed for SQL Server overhead, thereby reducing the memory available for the data (buffer) and procedure caches. Consequently, more physical memory may be required to maintain cache performance levels.
Locks—The locks parameter sets the number of available locks for SQL Server. Locks are used for concurrency control. The default value is set at 5,000 locks.
Note You should consider increasing the lock value to 10,000.

Performance impact—Each lock consumes 60 bytes of memory. Therefore, increasing the number of locks will increase the amount of memory needed for SQL Server overhead, thereby reducing the memory available for the data (buffer) and procedure caches. Consequently, more physical memory may be required to maintain cache performance levels.
Open objects—The open objects parameter sets the number of database objects that can be open at one time on SQL Server. The default is 500.
Note You should not hesitate to increase the open objects value to 1,000 for many situations; consider increasing it to as high as 5,000.

Performance impact—Each open object requires 240 bytes of memory. Therefore, increasing the number of open objects will increase the amount of memory needed for SQL Server overhead, thereby reducing the memory available for the data (buffer) and procedure caches. Consequently, more physical memory may be required to maintain cache performance levels.
Fill factor—The fill factor determines how full SQL Server will make each index page when creating new indexes. The default value is 0.
Performance impact—The fill factor percentage affects SQL Server performance because SQL Server must split an index page when it becomes full. Thus, maintaining a small fill factor on an index that is associated with a very dynamic table results in fewer page splits, fewer index entries per page, and a longer index page chain. Conversely, a high fill factor on a read-only table is perfectly acceptable and results in full index pages and a shorter index page chain.
Time slice—The time slice parameter sets the number of times a user process is allowed to pass through a yield point without voluntarily yielding to the next process.
Performance impact—If time slice is set too low, SQL Server will spend too much time switching between processes. Conversely, if time slice is set too high, waiting processes can experience long response times. The default of 100 is generally adequate and should seldom be changed.
Max worker threads—This parameter allows you to control the number of threads allocated to the pool of user threads. This can be useful when you have hundreds or thousands of user connections to SQL Server, where the overhead of managing hundreds or thousands of operating system threads would impair performance. When you limit the number of SQL Server working threads, user requests are executed on the next available worker thread. This parameter defaults to 255 and can be increased to 1,024.
Note Do not change this value from its default except when you are directed to do so by your primary support provider.
Tempdb in RAM—This option can improve performance when processing involves sorting, group by, or joins without supporting indexes. Allocating memory to tempdb effectively reduces the amount of memory available to allocate to the SQL Server data cache. Accordingly, you need enough physical memory to store the entire tempdb in RAM without impacting the memory required for Microsoft Windows NT Server, SQL Server, and applications. Thus, you should only consider using tempdb in RAM when you have large amounts of memory available. The default is off (0).
Performance impact—Forcing tempdb into RAM can result in increased performance if a significant amount of processing involves the creation and use of worktables by the SQL Server optimizer. Execution of such processing in RAM is inherently faster than corresponding disk I/O from paging.
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. In addition, this value is used along with the amount of activity on each database to calculate when to do a checkpoint on each database.
Performance impact—With the lazy writer process available in SQL Server, it is not necessary to tune the checkpoint interval for optimum performance. In fact, you can set the recovery interval very high to ensure that checkpointing occurs infrequently when performance is important. Remember however, that increasing the period between checkpoints also increases the time SQL Server takes to perform automatic recovery when the database is restarted after an ungraceful shutdown (power failure, hardware failure, and so on).

When configuring your SQL Server, start with the default values and experiment with changing parameter values once you have obtained a baseline of performance. When adjusting parameters to tune performance, adjust one parameter at a time and measure the difference in performance; changing multiple parameters in an ad hoc fashion is generally not productive.