Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Max Worker Threads Setting in SQLServer Expand / Collapse
Author
Message
Posted Monday, November 22, 2010 1:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:23 AM
Points: 250, Visits: 745
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.

Post #1024194
Posted Monday, November 22, 2010 2:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
The default value is 255. Please refer this for more information.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #1024210
Posted Monday, November 22, 2010 3:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:23 AM
Points: 250, Visits: 745
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.
Post #1024213
Posted Monday, November 22, 2010 3:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #1024216
Posted Monday, November 22, 2010 3:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,174, Visits: 36,569
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 2008, MVP
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

Post #1024217
Posted Monday, November 22, 2010 3:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #1024218
Posted Monday, November 22, 2010 5:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:23 AM
Points: 250, Visits: 745

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.
Post #1024257
Posted Monday, November 22, 2010 5:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
Yes Ravichandra, I would leave it at 0

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #1024261
Posted Thursday, December 2, 2010 3:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:23 AM
Points: 250, Visits: 745

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.

Post #1029129
Posted Thursday, December 2, 2010 4:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #1029155
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse