How to determine proper SQL Server configuration settings [ZT - from MS]
Posted on 2008-02-27 12:55 挥辉 阅读(539) 评论(0) 编辑 收藏 举报How to determine proper SQL Server configuration settings
| Article ID | : | 319942 |
| First Published | : |
3/18/2002 |
| Last Reviewed | : |
12/22/2005 |
| Revision | : | 4.0 |
| Modification Type | : | Major |
| Language Locale | : | en-us |
| Article Status | : | Published |
| Confidentiality | : | Public |
MICROSOFT INTERNAL SUPPORT INFORMATION
Please do not make changes to this article without first contacting the author or the SQL Server CPR EE.
IN THIS TASK
| • | SUMMARY | ||||||||||||||
| • |
| ||||||||||||||
| • | REFERENCES |
SUMMARY
This article describes the following configuration settings and considerations for their use:
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
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
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.
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:
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
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
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:
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
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
| • | Affinity Mask |
| • | Lightweight Pooling |
| • | Max Async IO |
| • | Max Worker Threads |
| • | Memory |
| • | Priority Boost |
| • | Set Working Set Size |
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). |
MICROSOFT INTERNAL SUPPORT INFORMATION
There are known bugs where fibers can cause problems with SQL Server. When you are working a case with a customer, be sure to check the lightweight pooling configuration setting and also look for bugs and articles that refer to lightweight pooling , such as:
308604 PRB: SQLMail is not supported when you run the server in fiber mode
303120 FIX: ConnectionWrite error when you use lightweight pooling
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" |
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:
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 PRB: There may not be enough virtual memory with large number of databases
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
REFERENCES
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
310834 PRB: Common causes of error message 844 or error message 845 (buffer latch time out errors)
298475 How to troubleshoot application performance issues
243588 How to troubleshoot the performance of ad-hoc queries
back to the top The information in this article applies to:
| • | Microsoft SQL Server 2000 (all editions) |
| • | Microsoft SQL Server 2000 64 bit (all editions) |
| • | Microsoft SQL Server 7.0 |
| • | 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 |
Keywords: |
kbyukonsweep kbappliestoyukon kbsqlsp3aswept kbSLQ64swept kbHOWTOmaster KB319942 |
| Query Words: | sp_configure configure performance tuning monitoring monitor recommend best practice |
| Intended Audience: | kbAudDeveloper |
