For SQL Server configuration information on SQL Server versions later than SQL Server 6.5 see Microsoft Knowledge Base article:
HOW TO: Determine Proper SQL Server Configuration Settings
SQL Server can provide very high performance with relatively littleconfiguration tuning. For example, Microsoft's IS department has a SQLServer that supports over 4,000 concurrent users. Other than obvioussettings such as memory, locks, and user connections, it uses mostlydefault configuration settings. Yet it produces very high performancerunning on an industry standard quad-processor computer. This high level ofperformance was facilitated by good application and database design, not byextensive 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:
Optimizing Microsoft SQL Server Performance
When addressing a performance problem, the degree of improvement availablefrom configuration tuning is typically modest, unless the system is alreadynot 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 serveror make it behave erratically. Except for obvious adjustments such asmemory, locks, and user connections, careful thought should be given beforeadjusting other settings, especially the advanced settings. Do not blindlyadjust them to a given value solely on the recommendation of a magazinearticle, white paper, or consultant if the new settings conflict withrecommendations from Microsoft Technical Support.
Years of support experience with hundreds of sites indicates that improperconfiguration settings may have results ranging from neutral to highlynegative. In particular, the settings below should not be adjusted fromdefaults without specific overwhelming reason. They should also not beadjusted without rigorous methodical performance testing both before andafter the change to assess the degree of improvement.
set working set size
max worker threads
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 givessome 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:
110983smp concurrency (6.5 only)
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) watchdogdriver may become CPU starved, assume the Windows NT Server operatingsystem 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, andmemory management functions. When the operating system is servicing thesefrequent calls, it uses CPU resources, and the default setting ensures thatthe operating system gets the CPU time it needs to quickly service SQLServer requests. Hence, all processors on a busy SQL Server system tend tobe 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 -1should be used, and why the setting is provided. Under a pure onlinetransaction processing (OLTP) load, with strictly controlled transactionpath lengths, where no ad-hoc or decision support queries are allowed, andin 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 sideeffects.
However, if the characteristics of the query stream feeding the server everchange from the above pure state, you may observe erratic behavior. Forexample, a system that appeared to run properly for a period of time maysuddenly stop responding. Debugging may be complicated, because it may havethe appearance of an operating system problem.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
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 byreducing the procedure cache setting does not appreciably affect the buffercache hit ratio, which is usually relatively flat across small changes(percentage-wise) in cache size.