SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Max Worker Threads Setting in SQLServer


Max Worker Threads Setting in SQLServer

Author
Message
ravisamigo
ravisamigo
Say Hey Kid
Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)

Group: General Forum Members
Points: 679 Visits: 760
Dear All,

Can some one please assist me on the below point.

In our environment, Max Worker Threads Setting has been set to 500 but Best Practice Analyzer suggested to set it to 0.

Please advise.

Thanks and Regards,
Ravichandra.
Adiga
Adiga
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3220 Visits: 21012
The default value is 255. Please refer this for more information.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
ravisamigo
ravisamigo
Say Hey Kid
Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)

Group: General Forum Members
Points: 679 Visits: 760
Thanks, But it would be better to set max worker threads to 288 as per the below link and also default setting for SQLServer2005 is 0.

http://sqlblogcasts.com/blogs/thepremiers/archive/2007/05/17/max-worker-threads-configuration-in-sql-server-2005.aspx

Note: we have 32-bit O/s,SQLServer2005 and 8 processors(32 GB Memory).


Please advise.

Thanks and Regards,
Ravichandra.
Adiga
Adiga
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3220 Visits: 21012
ravisamigo (11/22/2010)
Thanks, But it would be better to set max worker threads to 288 as per the below link and also default setting for SQLServer2005 is 0.


Since this question is posted under SQL 2000 forum, I had given the URL for SQL Server 2000.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
GilaMonster
GilaMonster
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114385 Visits: 45494
If you have SQL 2005, why are you posting in the SQL 2000 forums? People are going to assume you are running SQL 2000 and give you an answer appropriate to that version.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Adiga
Adiga
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3220 Visits: 21012
It is better to leave this settings to Dynamic. Also the link that you had provided mentions how SQL Server calculates that value "automatically".

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
ravisamigo
ravisamigo
Say Hey Kid
Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)

Group: General Forum Members
Points: 679 Visits: 760
Apologies, I will post the queries accordingly going forward.

Thanks Pradeep.

You mean,I can leave max worker threads Option to default i.e, 0 in SQLServer 2005.


Is it fine ?

Regards,
Ravichandra.
Adiga
Adiga
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3220 Visits: 21012
Yes Ravichandra, I would leave it at 0

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
ravisamigo
ravisamigo
Say Hey Kid
Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)

Group: General Forum Members
Points: 679 Visits: 760
Thanks Pradeep,

Iam facing the below error after changing the Max Worker Threads Setting from 500 to 0.

In Event viewer and SQLServerLog

Error: 18456, Severity: 14, State: 27.Login failed for user 'xxxxxx'. [CLIENT: 192.168.30.180]
Error: 18056, Severity: 20, State: 27.The client was unable to reuse a session with SPID 187, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
-------------------------------------------------------------------------

in SQLServer Agent Error log

"2010-11-29 10:05:44 -
! [382] Logon to server '(local)' failed (ConnUpdateJobActivity_NextScheduledRunDate) 2010-11-29 18:08:29
! [298] SQLServer Error: 258, Shared Memory Provider: Timeout error [258]. [SQLSTATE 08001] 2010-11-29 18:08:29
! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00] 2010-11-29 18:08:29
! [298] SQLServer Error: 258, Unable to complete login process due to delay in prelogin response [SQLSTATE 08001] "


Can you please advise whether this issue is related to Max Worker Threads Setting and
also I can see CPU usage is 100 %.

Your quick response is much appreciated.

Thanks and Regards,
Ravichandra.
Adiga
Adiga
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3220 Visits: 21012
I doubt this error has to do anything with Max Worker Thread option. The error Error: 18456, Severity: 14, State: 27 means SQL Server could not determine the initial database for that login.

Also check which process is making the CPU spike.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search