Configure server and service account settings for performance
In this we will learn about configuring server and service account settings for performance.
SQL Server can obtain a very high level of performance with relatively little configuration tuning. Moreover, you can obtain high levels of performance by using good application and database design, and not by extensive configuration tuning.
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. Further, in SQL Server version 7.0 and later, SQL Server uses automatic configuration tuning and it is extremely rare that configuration settings need any changes.
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. Moreover, 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.
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. However, 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.
- Firstly, large multi-processor servers are in use.
- Secondly, all servers are running at or near maximum capacity.
- Lastly, a lot of context switching occurs (greater than 20,000 per second).
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. Further, 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.
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. That is to say, you can only establish 255 user connections. However, 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.
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. However, 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.
Further, 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.
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. Further, 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.
