|Number of CPUs||32-bit computer||64-bit computer|
|<= 4 processors||256||512|
This value seems to work ok in most cases, however in certain situations, it needs to be checked and adjusted.
Number of threads in use:The total number of system threads is found in performance meter or from the Windows tasks manager; in the performance tab:
The number of SQL Server running threads can be found from performance monitor, or from this query:
select count(*) from sys.dm_os_threads
If the limit is being approached then you need to look at increasing the number of threads. Most threads are pooled - and only used when required, however operations such as replication, and in particular database mirroring, dedicate threads at database start that are never available to be reused.
I looked at system with over 100 databases that were being mirrored. The mirroring server was slow and unresponsive and when mirroring was configured on additional databases, any new connections to the database were denied.
Increasing the number of worker threads which was at 512 for a 64bit 4CPU system resolved the problem.
In the long term this site is likely to look at SQL 2012 Availability Groups.
When increasing the worker threads, Microsoft recommend 1024 as the maximum for 32-bit SQL Server and 2048 for 64-bit SQL Server.
for more information see MSDN - max worker threads option