How to determine proper SQL Server configuration settings (319942)

SUMMARY

This article describes the following configuration settings and considerations for their use:

• Affinity Mask
• Lightweight Pooling
• Max Async IO
• Max Worker Threads
• Memory
• Priority Boost
• Set Working Set Size

SQL Server can obtain a very high level of performance with relatively little configuration tuning. You can obtain high levels of performance by using good application and database design, and not by extensive configuration tuning. See the "References" section of this article for information about how to troubleshoot various SQL Server performance issues.

When you address a performance problem, the degree of improvement that is available from configuration tuning is typically modest unless you do not currently have the system properly configured. In SQL Server version 7.0 and later, SQL Server uses automatic configuration tuning and it is extremely rare that configuration settings (especially advanced settings) need any changes. Generally, do not make a SQL Server configuration change without overwhelming reason and not without careful methodical testing to verify the need for the configuration change. You must establish a baseline before the configuration change so that you can measure the benefit after the change.

If you do not have SQL Server properly configured, some settings might de-stabilize the server or might make SQL Server behave erratically. Years of support experience with many different environments indicate that non-default configuration settings might have results that range from neutral to highly negative.

If you do make a configuration change, you must perform rigorous methodical performance testing both before and after the change to assess the degree of improvement.

Based on actual support scenarios, SQL Server version 7.0 or later can achieve an extremely high level of performance without any manual configuration tuning.

In SQL Server 7.0 and later, do not make any configuration changes to user connections, locks, and open objects because, by default, SQL Server dynamically tunes these settings.

back to the top

Affinity Mask

The affinity mask setting refers to how firmly a thread is bound to a particular CPU. By default, Microsoft Windows NT and Microsoft Windows 2000 use "soft" affinity, which tries to re-schedule a thread on the CPU where it last executed. However, if this is not possible, a thread might run on a different CPU.

In actual practice, if you change the affinity mask setting from the default it only rarely helps performance, and will frequently degrade performance.

Affinity mask restricts SQL Server to a subset of available CPUs and permits other competing services better CPU access. In most cases, you do not need this because SQL Server runs at normal priority. The Windows NT or Windows 2000 thread scheduler dynamically adjusts thread priorities of all competing threads to make sure that they have a fair chance at all available CPUs.

Do not adjust affinity mask except under very unusual conditions. If you do choose to adjust affinity mask, perform rigorous methodical testing before and after the change to verify the need for and the degree of improvement.

back to the top

Lightweight Pooling

By default, SQL Server uses one thread per active SPID or user process. These threads work in a pooled configuration to keep the number of threads manageable. The advanced configuration option "lightweight pooling" (which is sometimes referred to as "Fiber mode") uses Windows NT "fiber" support to essentially handle several execution contexts with a single thread.

Based on actual production experience, you do not need to use Fiber mode except in very rare circumstances. Lightweight pooling is only even potentially useful if all of the following conditions are met. You must determine if it is actually useful through careful controlled testing.

• Large multi-processor servers are in use.
• All servers are running at or near maximum capacity.
• A lot of context switching occurs (greater than 20,000 per second).

To look for context switching, use Performance Monitor, select the counter threads, select the object Context switches/sec", and then select to capture all SQL Server instances. SQL Mail in SQL Server 2000 or in SQL Server 2005 is not supported if you run the server in Fiber mode. SQL Mail is not supported in SQL Server 2000 64 bit. For more information, see the "Differences Between 64-bit and 32-bit Releases" topic in SQL Server 2000 (64-bit Edition) Books Online. For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base: back to the top

Max Async IO

SQL Server 7.0: The max async IO configuration setting is available in SQL Server 7.0. It might be appropriate to change this setting if you have a fast RAID system and a way to measure the benefit. Do not change this setting unless you have a baseline by which to gauge the result. Monitor disk activity and look for any disk queuing issues. For additional information, please see the following SQL Server Books Online topics:

• "max async IO Option"
• "Monitoring Disk Activity"
• "Identifying Bottlenecks"

SQL Server 2000 or SQL Server 2005: In SQL Server 2000 or in SQL Server 2005, you cannot change the max async IO configuration setting. SQL Server 2000 or SQL Server 2005 automatically tunes this setting.

back to the top

Max Worker Threads

By default, the max worker threads setting is 255, which permits up to 255 worker threads to be created. Use the default setting of 255 in most cases. This does not mean that you can only establish 255 user connections. A system can have thousands of user connections (which are essentially multiplexed down to 255 worker threads) and in general, users do not generally perceive any delays. In such a case, only 255 queries can run concurrently, but this is multiplexed down to the number of available CPUs, so the concurrent nature is only perceived, regardless of the number of configured worker threads.

If you configure a number of worker threads to a value that is greater than the default, it is almost always counterproductive and slows performance because of scheduling and resource overhead. Only increase this setting under very unusual circumstances and when rigorous methodical testing demonstrates that it is useful to do so.

back to the top

Memory

See the SQL Server Books Online topic "Optimizing Server Performance Using Memory Configuration Options" for information about configuring memory.

For more information about configuring memory for clustered SQL Servers see "Usage Considerations" in the SQL Server Books Online topic, "Creating a Failover Cluster."

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base: back to the top

Priority Boost

By default, the priority boost setting is 0, which causes SQL Server to run at a normal priority whether you run SQL Server on a uniprocessor computer or on a symmetric multiprocessor (SMP) computer. If you set priority boost to 1, the SQL Server process runs at a high priority. This setting does not make the SQL Server process run at the highest operating system priority.

Based on actual support experience, you do not need to use priority boost for good performance. If you do use priority boost, it can interfere with smooth server functioning under some conditions and you should not use it except under very unusual circumstances. For example, Microsoft Product Support Services might use priority boost when they investigate a performance issue.

IMPORTANT Do not use priority boost for clustered servers that are running SQL Server 7.0, SQL Server 2000, or SQL Server 2005.

back to the top

Set Working Set Size

Do not change set working set size from the default setting. With the default of 0, the Windows NT or Windows 2000 virtual memory manager can determine the working set size of SQL Server. When you install SQL Server, Setup automatically instructs Windows NT or Windows 2000 to optimize performance for network applications. The Windows NT or Windows 2000 virtual memory manager will therefore do very little working set trimming, which only minimally interferes with the working set of SQL Server instances.

Changing this setting does not typically provide any performance benefit. Based on actual support cases, the changing of this setting typically causes more damage than good.

If you change set working set size, it can also be a cause of SQL Server error messages 844 or 845. See the "References" section in this article for more information about common causes of the 844 and 845 error messages.

back to the top