DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 319942 - Last Review: December 4, 2009 - Revision: 6.0

This article was previously published under Q319942

On This Page

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 and later can achieve an extremely high level of performance without any manual configuration tuning.

In SQL Server version 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.

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.

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:
308604  (http://support.microsoft.com/kb/308604/ ) PRB: SQLMail is not supported when you run the server in fiber mode
303120  (http://support.microsoft.com/kb/303120/ ) FIX: ConnectionWrite error when you use lightweight pooling

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 and later: In SQL Server 2000 and later, you cannot change the max async IO configuration setting. SQL Server 2000 and later automatically tunes this setting.

Max Worker Threads

By default, the max worker threads setting is 255 in SQL Server 2000. Therefore, up to 255 worker threads can 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.

Note By default, the max worker threads setting is 0 in SQL Server 2005 and in SQL Server 2008.

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.

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:
274750  (http://support.microsoft.com/kb/274750/ ) How to configure memory for more than 2 GB in SQL Server
224818  (http://support.microsoft.com/kb/224818/ ) Simple memory tuning is required if both SQL Server 7.0 and Exchange 5.5 Service Pack 2 are installed on BackOffice Small Business Server 4.5
316749  (http://support.microsoft.com/kb/316749/ ) PRB: There may not be enough virtual memory with large number of databases

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 and later.

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.

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
310834  (http://support.microsoft.com/kb/310834/ ) PRB: Common causes of error message 844 or error message 845 (buffer latch time out errors)
298475  (http://support.microsoft.com/kb/298475/ ) How to troubleshoot application performance issues
243589  (http://support.microsoft.com/kb/243589/ ) How to troubleshoot slow-running queries on SQL Server 7.0 or later
243588  (http://support.microsoft.com/kb/243588/ ) How to troubleshoot the performance of ad-hoc queries
224587  (http://support.microsoft.com/kb/224587/ ) How to troubleshoot application performance with SQL Server
166967  (http://support.microsoft.com/kb/166967/ ) Proper SQL Server 6.5 configuration settings
254321  (http://support.microsoft.com/kb/254321/ ) Clustered SQL Server do's, don'ts, and basic warnings
297864  (http://support.microsoft.com/kb/297864/ ) Performance considerations for an upgrade from SQL Server 6.5

APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
Keywords: 
kbhowtomaster KB319942
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support