question

xnl28 avatar image
xnl28 asked

How can I tell what value of "max worker threads" is?

Microsoft say that if the "max worker threads" option is set to 0, then SQL "automatically configure the number of worker threads at startup" ( http://msdn.microsoft.com/en-us/library/ms187024.aspx). I have set "max worker threads" to 0. How can I tell what value SQL Server has automatically configured and is running with? Note that running sp_configure shows "max worker threads" with both config_value and run_value as 0.
sql-server-2008-r2configuration
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
The answer is in the same link that you have given >The default value for max worker threads, 0, allows SQL Server to automatically configure the number of worker threads at startup. This setting is best for most systems; however, depending on your system configuration, setting max worker threads to a specific value sometimes improves performance. >The following table shows the automatically configured number of max worker threads for various combinations of CPUs and versions of SQL Server. Number of CPUs 32-bit computer 64-bit computer
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Yup sure it's 16! Here's another source as well about determining how many are actually being used, by using DMVs
3 Likes 3 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
I think it would count how many are 'presented' to SQL Server i.e. 16 but I'll check........
2 Likes 2 ·
xnl28 avatar image xnl28 commented ·
Oh, you are quite right, not sure how I missed that! My SQL Server has 2 x 64-bit quad-core hyperthreaded CPUs. So does SQL Server treat this as 2, 8, or 16 CPUs?
0 Likes 0 ·
xnl28 avatar image xnl28 commented ·
Excellent! Many thanks, Kev.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.