Running out of workers

  • Is there a way to set up a trace that will show why you are running out of workers?

    I know that I will need to capture blocking, but is there a way to track the workers themselves?

  • What errors/messages suggest that you're running out of worker threads?

    What version of SQL, what processor architecture, how many cores?

    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
  • Unable to log into the server and ...

    SQL Server Alert System: 'Out of Workers' occurred on <Server Name>

    DATE/TIME:7/17/2013 8:53:43 AM

    DESCRIPTION:New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 300 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: 0%. System Idle: 95%.

    COMMENT:(None)

  • IT will not allow us to use the SA account, so there is no way.. that I know of .. to get into the server when it is effected. Once you can get in.. my blocking query shows no problems, and running a listing of workers shows just the normal stuff.

  • GilaMonster (8/8/2013)


    What version of SQL, what processor architecture, how many cores?

    The DAC connection requires a sysadmin, not necessarily SA, so you should be able to use that to connect.

    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
  • This video[/url] talks about exactly the problem you're having.

    John

  • If you run out of workers, this is likely to be due to one of the following (or a combination thereof):

    1) There are simply too many active users.

    2) There are too many parallel queries.

    3) You have set "Max worker threads" to a low value.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • GilaMonster (8/8/2013)


    GilaMonster (8/8/2013)


    What version of SQL, what processor architecture, how many cores?

    The DAC connection requires a sysadmin, not necessarily SA, so you should be able to use that to connect.

    When the Sql Server instance stops responding.. and I try to connect using Management Studio, it times out.

    We are running 2008 R2 Standard, two CPU's with 6 cores each.

  • Did you follow the link I posted to watch the video? You need to use DAC.

    John

  • Erland Sommarskog (8/8/2013)


    If you run out of workers, this is likely to be due to one of the following (or a combination thereof):

    1) There are simply too many active users.

    2) There are too many parallel queries.

    3) You have set "Max worker threads" to a low value.

    Worker threads are set to zero, default.

  • John Mitchell-245523 (8/9/2013)


    Did you follow the link I posted to watch the video? You need to use DAC.

    John

    Not yet.. still reviewing the posts.

  • John Mitchell-245523 (8/9/2013)


    Did you follow the link I posted to watch the video? You need to use DAC.

    John

    I am getting an error that DAC is not supported. I will put in a request to IT, but they have locked the crap out of our new servers.

  • dwilliscp (8/9/2013)


    We are running 2008 R2 Standard, two CPU's with 6 cores each.

    32 bit or 64 bit?

    Have you enabled remote DAC? If not, you'll have to connect from the server directly.

    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
  • GilaMonster (8/9/2013)


    dwilliscp (8/9/2013)


    We are running 2008 R2 Standard, two CPU's with 6 cores each.

    32 bit or 64 bit?

    Have you enabled remote DAC? If not, you'll have to connect from the server directly.

    Sorry should have added.. 64bit. Any change that would effect Disaster Recover, has to be done by the App Admin team. So I put in a request, and will see if they make the change. I know when I asked to get worker threads set to 1,000 from the 800 or so that it currently is.. they wanted me to prove that the problems we are having was worker threads. Thus this posting.. on trying to track their use and what happens when they run out.

  • I wouldn't recommend changing worker threads. 0 is a good default, it shouldn't need changing. Identifying why you're running out of worker threads should be a priority.

    Maybe schedule a job to run every couple minutes that runs various diagnostic scripts and inserts results into a monitoring database.

    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

Viewing 15 posts - 1 through 15 (of 42 total)

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