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 PRB: SQLMail is not supported when you run the server in fiber mode
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 How to configure memory for more than 2 GB in SQL Server
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
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.