INF: Proper SQL Server Configuration Settings

ID: Q166967

The information in this article applies to:

SUMMARY

SQL Server can provide very high performance with relatively little configuration tuning. For example, Microsoft's IS department has a SQL Server that supports over 4,000 concurrent users. Other than obvious settings such as memory, locks, and user connections, it uses mostly default configuration settings. Yet it produces very high performance running on an industry standard quad-processor computer. This high level of performance was facilitated by good application and database design, not by extensive configuration tuning.

For more information on SQL Server performance optimization, see the following article in the Microsoft Knowledge Base:

Q110352 Optimizing Microsoft SQL Server Performance
When addressing a performance problem, the degree of improvement available from configuration tuning is typically modest, unless the system is already not configured properly.

If it is not configured properly, some settings may destabilize the server or make it behave erratically. Except for obvious adjustments such as memory, locks, and user connections, careful thought should be given before adjusting other settings, especially the advanced settings. Do not blindly adjust them to a given value solely on the recommendation of a magazine article, white paper, or consultant if the new settings conflict with recommendations from Microsoft Technical Support.


MORE INFORMATION

Years of support experience with hundreds of sites indicates that improper configuration settings may have results ranging from neutral to highly negative. In particular, the settings below should not be adjusted from defaults without a clear understanding of the impact this change will have. If they are adjusted from their defaults and you encounter a problem, you may be advised by Microsoft Technical Support to restore the default settings while analyzing the problem.

set working set size
smp concurrency
priority boost
max worker threads
procedure cache
Other settings such as tempdb in RAM, free buffers, max async I/O, read ahead parameters, and so on, should also not be casually adjusted without a good understanding of the impact, or without objective performance tests to verify the benefit.

The remainder of this article discusses each of these parameters and gives some considerations in using them:

"set working set size": This setting defaults to 0, which allows the Windows NT Server virtual memory manager to determine the working set size of SQL Server. Under most conditions, this is the correct setting. When SQL Server is installed, the setup program automatically selects "maximize throughput for network applications" in the Network Control Panel under the server service properties. This instructs the Windows NT Server virtual memory manager to do very little working set trimming, thus minimally interfering with SQL Server's working set. For this reason, it is not usually necessary to override the "set working set size" default. However, if you set it to 1, upon startup SQL Server uses the Win32 API SetProcessWorkingSetSize() to more rigidly fix the working set. If you then overcommit SQL Server memory, the operating system is less able to compensate, and you may more readily see memory-related operating system errors. For more information, see the following article in the Microsoft Knowledge Base:
Q110983 Recommended SQL Server for NT Memory Configurations
"smp concurrency": This setting defaults to 0, which allows SQL Server (at startup time) to automatically detect the number processors and use n-1 of these. On an SMP computer, it also raises the process priority of SQL Server to 15. The vast majority of the time, you should use the default setting. Overriding this setting to -1 removes any restrictions on how many processors SQL Server can use, which may starve the operating system of CPUs under certain conditions. Typical symptoms include network errors or a console that has stopped responding. The NET VIEW command may fail with a "network session was canceled" error. Other processes running on the computer may stop responding, due to CPU starvation. This may include other services, SQL Executive, backup programs, batch jobs, and so on.

On Compaq Proliant servers, the Automatic Server Recovery (ASR) watchdog driver may become CPU starved, assume the Windows NT Server operating system has failed, and restart the server to recover.

The default "smp concurrency" setting of 0 does not "waste" a processor, because SQL Server calls the operating system for all network, I/O, and memory management functions. When the operating system is servicing these frequent calls, it uses CPU resources, and the default setting ensures that the operating system gets the CPU time it needs to quickly service SQL Server requests. Hence, all processors on a busy SQL Server system tend to be used when the default "smp concurrency" setting of 0 is used. Setting "smp concurrency" to 1, 2, and so on, should also not be necessary.

This may raise the question of when an "smp concurrency" setting of -1 should be used, and why the setting is provided. Under a pure online transaction processing (OLTP) load, with strictly controlled transaction path lengths, where no ad-hoc or decision support queries are allowed, and in an environment where the computer is absolutely dedicated to SQL Server (the console is not used, nor file sharing, nor SQL Executive, and so on) this setting can provide some performance improvements with minimal side effects.

However, if the characteristics of the query stream feeding the server ever change from the above pure state, you may observe erratic behavior. For example, a system that appeared to run properly for a period of time may suddenly stop responding. Debugging may be complicated, because it may have the appearance of an operating system problem.

For more information, see the following article in the Microsoft Knowledge Base:
Q111405 SQL Server and Windows NT Thread Scheduling
"priority boost": This setting defaults to 0, which causes SQL Server to run at process priority 7 on a uniprocessor computer and (usually) 15 on an SMP computer. Use the default setting in most circumstances. Setting it to 1 boosts the SQL Server process priority to 15 on a uniprocessor computer, and to 24 on an SMP computer. This can have effects similar to setting "smp concurrency" to -1.
"max worker threads": This setting defaults to 255, which allows up to 255 worker threads to be created. Use the default setting of 255 in most cases. This does not mean that only 255 user connections can be established. A system can have thousands of user connections, essentially multiplexed down to 255 worker threads, and each user will generally perceive no delays. In such a case, only 255 queries can be "concurrently" running, but this in turn is further multiplexed down to the number of available CPUs, so the concurrent nature is only perceived, no matter how many worker threads are configured.

As each new user connection is established, a new worker thread is created, up to the worker thread limit. After a period of inactivity, a worker thread automatically times out and closes. This is to keep overhead low, because each thread consumes resources. Configuring high numbers of worker threads is usually counterproductive and slows performance, due to scheduling and resource overhead. "procedure cache": On SQL Server 6.5, this setting defaults to 30, which indicates 30 percent of SQL Server's cache space will be reserved for the procedure cache. On a system with a great deal of memory (say 256 MB or more), this may possibly be reduced somewhat to allow more memory for the buffer cache. However, do not blindly reduce this setting to a very low number (such as 5 percent), even if the system uses few stored procedures. This is because procedure cache is a general purpose memory pool used for a variety of internal server allocation functions, not just for caching stored procedures. Without a sufficient amount of memory in this pool, you may receive errors or observe other erratic behavior. On SQL Server 6.5, you can monitor the use of the procedure cache with Performance Monitor, and decide whether to reduce the procedure cache setting.

In many circumstances, the incremental amount of buffer cache freed up by reducing the procedure cache setting does not appreciably affect the buffer cache hit ratio, which is usually relatively flat across small changes (percentage-wise) in cache size.

Additional query words: 


Keywords          : kbenv kbusage SSrvGen 
Version           : winnt:4.2x,6.0,6.5
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: July 2, 1999