Post reply

Max Worker Threads Setting in SQLServer

  • November 22, 2010 at 1:56 am

    #157329

    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.

  • November 22, 2010 at 2:59 am

    #1253637

    The default value is 255. Please refer this for more information.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • November 22, 2010 at 3:11 am

    #1253638

    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.

  • November 22, 2010 at 3:33 am

    #1253641

    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

  • November 22, 2010 at 3:34 am

    #1253642

    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

  • November 22, 2010 at 3:35 am

    #1253643

    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

  • November 22, 2010 at 5:54 am

    #1253675

    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.

  • November 22, 2010 at 5:59 am

    #1253678

    Yes Ravichandra, I would leave it at 0

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • December 2, 2010 at 3:02 am

    #1257770

    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.

  • December 2, 2010 at 4:43 am

    #1257788

    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

  • December 2, 2010 at 5:05 am

    #1257794

    Thanks Pradeep.

    Can you clear this up for me please.

    Regards,

    Ravichandra.

  • December 2, 2010 at 5:39 am

    #1257806

    You can make use of Profiler, perfmon among others to track High CPU utilization issue. You may find this article useful.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply