Determine the correct SQL Server configuration settings

Server

Describes the following configuration settings and what to consider when using: • Similarity mask • Lightweight pool • Max asynchronous IO max number of worker threads • Memory • Priority promotion • Set working set size

SQL Server can achieve very advanced performance with minimal configuration tuning. Advanced performance can be achieved by using good application and database design rather than through a large number of configuration tweaks. For information about how to troubleshoot various performance issues with SQL Server, see the "References" section of this article.

When dealing with performance issues, the level of improvement achieved by adjusting the configuration is usually not significant unless the system is currently configured correctly. SQL Server uses automatic configuration tuning in SQL Server version 7.0 and later, and configuration settings (especially advanced settings) rarely require changes. In general, do not change the SQL Server configuration when there are no compelling reasons and if you do not carefully conduct a systematic test to verify the need for a change configuration. Before you change the configuration, you must establish a baseline so that you can measure the benefits of the change.

If SQL Server is configured incorrectly, some settings may degrade the server's stability or may cause SQL Server to behave abnormally. Years of support experience with many different environments indicate that the results of Non-default configuration settings may be between neutral and very negative.

If you do change the configuration, you must perform rigorous and systematic performance testing before and after the change to assess the level of improvement.

Depending on the actual support scenario, SQL Server version 7.0 or later can achieve extremely advanced performance without any manual configuration tuning.

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

Similarity mask

A similarity mask setting is the degree to which a thread is bound to a specific CPU. By default, Microsoft Windows NT and Microsoft Windows 2000 use "soft" affinity, which attempts to reschedule the thread on the last CPU that executes the thread. However, if this operation is not implemented, the thread may run on another CPU.

In practice, if you change the default values for the similarity mask setting, you will rarely improve performance and often degrade performance.

The affinity mask restricts SQL Server to a subset of the available CPUs and allows other competitive services to gain better access to the CPU. In most cases, because SQL Server runs at normal priority, you do not have to do this. Windows NT or Windows 2000 thread scheduler dynamically adjusts the thread priority of all competing threads to ensure that they have equal opportunities in all available CPUs.

Do not adjust the affinity mask, except in very special cases. If you do choose to adjust the similarity mask, perform rigorous, systematic testing before and after the change to verify the need for improvement and the extent of the improvement.

Light weight Pool

By default, SQL Server uses a thread for each active SPID or user process. These threads run in a pooled configuration to allow the number of threads to be managed. The Advanced configuration Option "lightweight pooling" (sometimes referred to as "fiber mode") uses Windows NT "fiber" support, which basically handles multiple execution contexts with a single thread.

Based on actual production experience, no fiber mode is required except in rare cases. A lightweight pool can be useful only if all of the following conditions are true. You must pass careful controlled tests to determine whether it is actually useful. • Large multiprocessor servers are in use.

• All servers are running at maximum capacity or near maximum capacity.

• Many context switches have occurred (more than 20,000 times per second).

To find context switches, use Performance Monitor, select a counter thread, select Object Context Switches/sec, and then choose to capture all instances of SQL Server. If you are running the server in fiber mode, SQL Mail in SQL Server 2000 is not supported.

Max Asynchronous IO

The maximum asynchronous IO configuration setting is available in SQL Server 7.0:sql Server 7.0. If your RAID system is faster and you have the means to measure the benefits, it may be appropriate to change this setting. Do not change this setting unless you have a baseline to measure results. Monitor disk activity and find all disk queue issues. For additional information, see the following SQL Server online book topics:

• Max asynchronous IO option

• "Monitoring disk Activity"

• "Identify bottlenecks"

SQL Server 2000: In SQL Server 2000, the maximum asynchronous IO configuration setting cannot be changed. SQL Server 2000 automatically adjusts this setting.

Maximum number of worker threads

By default, the maximum number of worker threads is set to 255, which allows up to 255 worker threads to be created. In most cases, use the default setting of 255. This does not mean that you can only establish 255 user connections. A system can have tens of thousands of user connections (in its essential multiplexing to 255 worker threads), and generally, users do not feel any latency. In this case, you can run only 255 queries at the same time, but this will multiplex the number of available CPUs, so you can feel the concurrency attribute regardless of the number of worker threads configured.

If the number of worker threads is configured to be greater than the default, it will almost always reverse and degrade performance due to scheduling and resource overhead problems. This should be done only in very special circumstances, and when rigorous systematic testing shows that this setting is helpful.

Memory

For information about how to configure memory, see the SQL Server online book topic "Optimizing Server performance with memory configuration options."

For more information about how to configure memory for a clustered SQL Server, see "Using Considerations" in the SQL Server Books Online topic "Creating a Failover Cluster."

　Priority promotion

By default, the priority elevation is set to 0, so that whether you run SQL Server,sql Server on a single-processor computer or a symmetric multiprocessor (SMP) computer runs at normal priority. If you set the priority elevation to the 1,sql Server process, it will run at a higher priority level. This setting does not cause the SQL Server process to run at the highest operating system priority.

Depending on the actual support experience, you do not have to use priority elevation to achieve better performance. If you do use priority elevation, in some cases, you may interfere with the server's normal operation. Therefore, you should not use it except in very special circumstances. For example, Microsoft product Support Services may use priority elevation when investigating performance issues.

Important Do not use priority elevation for cluster servers that are running SQL Server.

Set the working set size

Do not change the default settings for setting the working set size. Use the default value 0,windows NT or the Windows 2000 virtual Memory Manager to determine the working set size of SQL Server. When you install SQL Server, Setup automatically instructs Windows NT or Windows 2000 to optimize the performance of your network applications. As a result, the Windows NT or Windows 2000 virtual Memory Manager makes minimal working set clipping, which minimizes the disruption to the SQL Server instance working set.

Changing this setting usually does not bring any performance benefit. Depending on the actual support case, changing this setting usually has more damage than it brings.

If you change the set working set size, you can also cause SQL Server to receive error messages 844 or 845.