Search This Blog

Tuesday, January 15, 2013

Handling MS SQL Server requiring Large Numbers of worker threads

SQL server since version 2005 has had a default of 0 for the number of worker threads, meaning that the value is set by SQL Server. In the books online the number of threads set vary by the number of processors for 32bit and 64 bit systems:

Number of CPUs 32-bit computer 64-bit computer
<= 4 processors 256 512
8 processors 288 576
16 processors 352 704
32 processors 480 960

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

No comments:

Post a Comment