SQL Server Performance

I am getting more 'Waiting for a Worker thread' in JOBS

Discussion in 'SQL Server 2005 General DBA Questions' started by kumaravel v, Sep 22, 2008.

  1. kumaravel v New Member

    Hi everyone
    I have more than 100 SQL Server jobs scheduled to run simultaneously and continuosly. Several of these jobs are not running and with the status 'Waiting for a Worker thread'.
    Can anyone please tell me what this status means and and how to avoid it ?

    I have tried increasing the CPU worker threads value, but did not help.

    Also, what is the max. number of jobs that we can run simultaneously or that SQL Server Agent can handle ?
  2. chetan_dba New Member

    It would mean that there is crunch in the Thread procesing. Any of the job would have taken most of the thread. so there are less number of the thread available for the jobs
  3. kumaravel v New Member

    thanks for you reply chetan.
    I have still more than 500 jobs has to be created. Because of this "waiting for worker Thread" i dont know
    what i have to proceed.
    Can you tell me how to resolve this issue? Or should i increase "maximum worker threads" in server property ?
  4. kjw0720 New Member

    Not sure what version of SQL you're running, but if it's SQL2000, check this article out: http://support.microsoft.com/default.aspx/kb/306457 (SQLAGENT job waiting on worker thread)
    It tells you how to change the registry to allow more jobs (depending on subsystem type) to execute concurrently.
    We had the same problem on our server. We had a lot of jobs that run DTS packages and the default of 10 for CmdExec was not sufficient. We changed it to 25 and have had much fewer problems.
    Hope this helps!
  5. kumaravel v New Member

    Thanks for your Reponse. I am using 2005 ver. I dont know wheather i can follow this article's tips.
  6. kjw0720 New Member

    From that same KB article (http://support.microsoft.com/kb/306457)
    'Note Microsoft SQL Server 2005 stores the max_worker_thread setting in the syssubsystems table in the msdb system database instead of in the registry. The syssubsystems table can be updated to reflect a non-default max_worker_thread setting.'
  7. virmahi New Member

    In my application there are around 75 concurrent users accessing application at one time through internet. We need to increase the users upto 400. The response time decreases once it reaches 100 users. Please advice a way to handle this. Can we do anything in the configuration to handle this.
    Please help me on this,
  8. satya Moderator

    Welcome to the forums Virmahi, I believe it will be good to open a new thread by specifying the confirguration and problem that is affecting.
    If your system is suffering from performance start from the queries that are generated on time to time basis, http://sqlserver-qa.net/blogs/perftune/archive/2008/02/05/3182.aspx is good to start.
  9. satya Moderator

    Run SP_CONFIGURE and get the value for Max Worker threads, post here.In general the default will be 255, so you need to increase the value carefully after considering the hardware & database usage.Part of that KBA still applicable in SQL 2005,for a better understanding for you on this worker threads are managed: After the total number of concurrent jobs that are running in the subsystem reaches the max_worker_thread setting, the additional job that starts in that subsystem is queued until one of the running jobs completes. This can be a problem for the LogReader subsystem. By default, the LogReader job is set to run continuously. If more that 25 LogReader jobs are started under the default LogReader subsystem setting, the queued jobs may never have the chance to run.Because the Transact-SQL subsystem default is 20, the maximum number of concurrent Transact-SQL jobs is reached quickly if several Transact-SQL jobs are set to run. Add the Transact-SQL subsystem value as a string with a value that is higher than 20 if you notice that the Transact-SQL jobs are being queued for this reason.
  10. kumaravel v New Member

    hi satya
    It is good, as you suggested for the previous guy's problem against the 'waiting for a worker thread'.
    I am facing this problem very long back in my sqlserver 2005. I have more than 600 to 700 jobs are running concurrently. My lot of jobs are showing 'Waiting for worker thread' status. The problem is, still i have to add another 200 and above jobs in future in the same server. Hope i have enough configuration in my server. i,e 16 gb ram with 8 CPU processor.
    When i execute sp_configure command, it shows the below name with min and max value. Not able to find out the max_worker_thread value.allow updates 0 1 0 0
    clr enabled 0 1 0 0cross
    db ownership chaining 0 1 0 0default
    language 0 9999 0 0max text repl size (B) 0 2147483647 65536 65536
    nested triggers 0 1 1 1remote
    access 0 1 1 1remote
    admin connections 0 1 0 0remote
    login timeout (s) 0 2147483647 20 20remote
    proc trans 0 1 0 0remote
    query timeout (s) 0 2147483647 600 600server trigger recursion 0 1 1 1
    show advanced options 0 1 0 0user
    options 0 32767 0 0I have increase my max worker threads upto 1500 from 0. Even though it shows 'waiting for... ' message. can i change min memory per query from 1024 into 2048 to increase the better perfomance?
    Kindly advice me what can i do to resolve this issue.
  11. satya Moderator

    Have you restarted after this configuration setting?
  12. kumaravel v New Member

    Yes. i have restarted sqlserver and agent too.
  13. Saurabh Srivastava New Member

    Hi Kumar,
    Reason you don't see all options is that SHOW ADVANCED OPTION run value is 0. Run this command to see all advanced sp configure options;
    ***************************************************************sp_configure 'show advanced option', 1
    In 2005 the default value for MAX WORKER THREAD setting has changed from 255 (SQL2k) to 0 (dynamic in SQL2k5). According to various sources, the following table shows the automatically configured number of Max Worker Threads for SQL Servers (2005) with different numbers of CPUs when the SQL Server database engine is left to dynamically set this value:

    CPUs Auto Configured Max Worker Threads (32 bit Server) Auto Configured Max Worker Threads (64 bit Server)
    <= 4 CPUs 256 512
    8 CPUs 288 576
    16 CPUs 352 704
    32 CPUs 480 960

    The formula to determine the max worker processes is as follows:
    For x86 systems where total number of logical processors <=4
    Number of max worker threads = 256 ( counting starts from 0)
    Number of max worker threads = 256 + ((# Procs – 4) * 8)
    For x64 systems where total number of logical processors <= 4
    Number of max worker threads = 512
    Number of max worker threads = 512 + ((# Procs – 4) * 16)
    For Example: On an 8 processor x86 machine (could be 4-proc HT or 4-proc dual core machine, therefore shows 8 logical procs), max worker threads will be configured to 288 (256 + (4 * 8))
    You can find out how many threads have been configured by looking at the max_workers_count column in the sys.dm_os_sys_info DMV.
    Each worker has a stack associated with it. The stack size can be found:
    select stack_size_in_bytes/(1024.0 * 1024.0) as stack_size_in_MB from sys.dm_os_sys_info
    Note:SQL and indeed Windows has NO method of identifying the difference between physical and logical processors – i.e. multi-core and hyper-threaded processors appear to windows (and therefore) SQL as processors. As such 4 x Dual core would appear as 8 logical processors – and max worker threads would be sized on this basis regardless of whether these are 8 x physical, 4 x dual core or 4 x HT.
    Please DO NOT modify these settings if system is performing optimally. In case, of performance issues first research and collect sufficient supportive data before making changes to this and/or any Server level setting/options.
  14. kumaravel v New Member

    thanks saurabh
    I got below configuration after i reconfigure the sp_configure.
    Ad Hoc Distributed Queries0100
    affinity I/O mask-2147483648214748364700
    affinity mask-2147483648214748364700
    affinity64 I/O mask-2147483648214748364700
    affinity64 mask-2147483648214748364700
    Agent XPs0111
    allow updates0100
    awe enabled0100
    blocked process threshold08640000
    c2 audit mode0100
    clr enabled0100
    cost threshold for parallelism03276755
    cross db ownership chaining0100
    cursor threshold-12147483647-1-1
    Database Mail XPs0100
    default full-text language0214748364710331033
    default language0999900
    default trace enabled0111
    disallow results from triggers0100
    fill factor (%)010000
    ft crawl bandwidth (max)032767100100
    ft crawl bandwidth (min)03276700
    ft notify bandwidth (max)032767100100
    ft notify bandwidth (min)03276700
    index create memory (KB)704214748364700
    in-doubt xact resolution0200
    lightweight pooling0100
    max degree of parallelism06400
    max full-text crawl range025644
    max server memory (MB)16214748364721474836472147483647
    max text repl size (B)021474836476553665536
    max worker threads1283276715001500
    media retention036500
    min memory per query (KB)512214748364710241024
    min server memory (MB)0214748364700
    nested triggers0111
    network packet size (B)5123276740964096
    Ole Automation Procedures0100
    open objects0214748364700
    PH timeout (s)136006060
    precompute rank0100
    priority boost0100
    query governor cost limit0214748364700
    query wait (s)-12147483647-1-1
    recovery interval (min)03276700
    remote access0111
    remote admin connections0100
    remote login timeout (s)021474836472020
    remote proc trans0100
    remote query timeout (s)02147483647600600
    Replication XPs0100
    scan for startup procs0100
    server trigger recursion0111
    set working set size0100
    show advanced options0111
    SMO and DMO XPs0111
    SQL Mail XPs0100
    transform noise words0100
    two digit year cutoff1753999920492049
    user connections03276700
    user options03276700
    Web Assistant Procedures0100
    xp_cmdshell0100I got the below one when i run your query.select
    stack_size_in_bytes/(1024.0 * 1024.0) as stack_size_in_MB from sys.dm_os_sys_infoResult :  1.996093750000
    probably u can see my real configuration what i have it in my server。  Still it shows the same "waiting for worker thread"
    -- kmr
  15. Saurabh Srivastava New Member

    If you are running 32-bit system bring down Max Worker Thread option to lower value. Because on 32-bit platform maximum is 1024 and on 64-bit max is 2048. Generally you don't need maximum threads to run jobs even if all of them running concurrently. Setting it to max values can cause other performance issues on your system which I'm sure you don't want. I can see your default trace option is true. I would suggest to go through those trace files find out entries when you run jobs and get errror. If your organization policy allows you can post errors from traces and sql agent errorlogs to help us understand this issue better to come up with stellar solution.
    I want you to do a simple test. Increase value of T-SQL subsyem in syssubsystems table in msdb and then execute your jobs.
  16. kumaravel v New Member

    As you said, i have changed the value into 572 in syssubsystems . Earlier it was 160.
    Will let you know after some time how it is performing.
    -- thanks for your help.
  17. Saurabh Srivastava New Member

    You have increased it by almost three times. I would do 25% increment to get baseline number. Anyway, let's see what happens
  18. MohammedU New Member

    May you are expereinecing the MemToLeave memory (Virtual Memory) issue if you are on 32 bit.
    Read the following Blog by one of Guru's Guru Late Ken Henderson...
    If the issue is not resolved, try to increase increase the MTL memory using -g switch...
  19. kumaravel v New Member

    thanks for your link mohammed
    I checked my Virtual Memory in my server which shows 30000 paging file size.
    My server configuration is, win server 2003, standard x64, 16 GB, Quad-Core.
    Is the virtual Memory is enough or highly configured ?

Share This Page