SUMMARY
For SQL Server configuration information on SQL Server versions later than SQL Server 6.5 see Microsoft Knowledge Base article:
319942 HOW TO: Determine Proper SQL Server Configuration Settings
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 additional information about on SQL Server performance optimization, click the article number below
to view the article in the Microsoft Knowledge Base:
110352 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. These should not generally be changed without overwhelming reason and not without careful methodical testing to verify the need to change and the benefit once changed.
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 specific overwhelming reason. They should also not be
adjusted without rigorous methodical performance testing both before and
after the change to assess the degree of improvement.
set working set size
smp concurrency
priority boost
max worker threads
procedure cache
Other settings such as tempdb in RAM, free buffers, read ahead parameters, and max async I/O, 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 additional information, click the article number below
to view the article in the Microsoft Knowledge Base:
110983 Recommended SQL Server for NT Memory Configurations
:
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, click the following article number to view the article in the Microsoft Knowledge Base:
111405
SQL Server and Windows NT Thread Scheduling
: 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.
Actual support experience shows enabling priority boost is not necessary for good performance in most situations. Setting boost priority on can interfere with smooth server functioning under some conditions, and should not be used except under very unusual circumstances. For example this might be used during investigating a performance by Microsoft Product Support Services. It should not be used just because a white paper or consultant so advises.
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. On SQL Server 6.5, after a period of inactivity, a worker thread automatically times out and is closed. 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.