locked
SQL Process Utilization: 1%. System Idle: 96% RRS feed

  • Question

  • Hi All,

    Could you please advise on the below error.

    "New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 1%. System Idle: 96%. "


    When I checked the errorlog there were more deadlocks appeared.


    Thank and Regards,
    Ravi.

    Sunday, June 3, 2012 4:37 AM

Answers

  • What version are you using? http://support.microsoft.com/kb/974205

    http://technet.microsoft.com/en-us/library/ms187024.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Sunday, June 3, 2012 6:53 AM
  • Sure, I will try to increase the 'Max worker Threads'  before that I want know How can I find the number of connenctions in sqlserver ?

    The query below will return the current number of user connections:

    SELECT COUNT(*) FROM sys.dm_exec_connections;
    Although increasing the number of threads may mitigate the warning message, it won't address the underlying cause.  I suggest you analyze server activity for optimization opportunities.

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, June 3, 2012 2:42 PM
    Answerer

All replies

  • What version are you using? http://support.microsoft.com/kb/974205

    http://technet.microsoft.com/en-us/library/ms187024.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Sunday, June 3, 2012 6:53 AM
  • Thanks for your reply.

    Version - SQLServer 2005 SP4 (9.00.5000.00).

    Edition - Standard.

    Maximum Worker Threads - Zero(0)

    Please advise how we can resolve this issue.

    Thanks and Regards,

    Ravichandra

    Sunday, June 3, 2012 11:15 AM
  • How much memory does the server have? Have you set up MAX memory param? What does the below return?


    --sql server uses the memory
    select 
    CONVERT(VARCHAR,CAST(bpool_committed *8  AS MONEY),1)AS [SIZE],
    bpool_committed,bpool_commit_target 
      from 
    sys.dm_os_sys_info

    Try increase Max workers threads if you have lots of connections....

    sp_configure 'show Advanced options' ,1
    go
    sp_configure 'Max worker Threads', 400
    go
    Reconfigure with Override

    'MAX WORKER THREADS' is basically meant for Keeping the Thread Readily Available in a pool for use for any new Request made to Database.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Sunday, June 3, 2012 12:25 PM
  • Thanks for your prompt reply.

    O/s - Windows Server 2008 X-86 based PC

    Total Memory  - 32 GB

    Min Memory - 10 GB and Max Memory - 30 GB

    AWE is also enabled

    Sure, I will try to increase the 'Max worker Threads'  before that I want know How can I find the number of connenctions in sqlserver ?

    Thanks and Regards,

    Ravi.

    Sunday, June 3, 2012 2:23 PM
  • No, before increasing  'Max worker Threads' please set MAX memory 22 GB and see if it works better

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Sunday, June 3, 2012 2:39 PM
  • Sure, I will try to increase the 'Max worker Threads'  before that I want know How can I find the number of connenctions in sqlserver ?

    The query below will return the current number of user connections:

    SELECT COUNT(*) FROM sys.dm_exec_connections;
    Although increasing the number of threads may mitigate the warning message, it won't address the underlying cause.  I suggest you analyze server activity for optimization opportunities.

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, June 3, 2012 2:42 PM
    Answerer
  • No, before increasing  'Max worker Threads' please set MAX memory 22 GB and see if it works better

    -----------------------------------------------------------------------------

    Max memory was already set to 30 GB.

    If I want to analyze using SQLProfiler, what are all the metrics needs to be consider ?

    Please advise.

    Thanks and Regards,

    Ravi

    Monday, June 4, 2012 5:10 PM
  • Can someone please advise me on this issue.

    And also I can see these errors in SQLSeverAgent log.

    [298] SQLServer Error: 258, Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]
    [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
    [298] SQLServer Error: 258, Shared Memory Provider: Timeout error [258]. [SQLSTATE 08001]
    [382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)

    Thanks and Regards,

    Ravi.

    Thursday, June 7, 2012 5:53 AM