INF: Proper SQL Server Configuration Settings

• Microsoft SQL Server, versions 4.2, 6.0, and 6.5

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:

   ARTICLE-ID: Q110352
   TITLE     : Optimizing Microsoft SQL Server Performance

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

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:

   ARTICLE-ID: Q110983
   TITLE     : Recommended SQL Server for NT Memory Configurations

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:

   ARTICLE-ID: Q111405
   TITLE     : SQL Server and Windows NT Thread Scheduling

"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.

Keywords          : SSrvGen kbenv kbusage
Version           : 4.2 6.0 6.5
Platform          : WINDOWS
Issue type        : kbinfo
Solution Type     : Info_Provided