SQL Agent MaxWorkerThreads and Agent subsystem

  • [アーティクル]

 

 

In SQL Server Agent Execution of each supported job step is handled by a subsystem. Except for TSQL steps, each subsystem is handled by an external .dll file that is loaded into SQL Agent’s process at startup. Each subsystem takes information about the job step to execute and interprets it according to its own definition.  

MaxWorkerThreads for SQL Agent

There is a MaxWorkerThreads for the SQLAgent process. By default it is set to (Numbrer of CPU * 100). You can increase this with the registry entry, HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\MSSQL.x\SQLSERVERAGENT. Add a DWORD MaxWorkerThreads and stop and start SQLAgent.

If you hit this limitation of MaxWorkerThreads you see a message [398].

+ [398]  The job (TigerEye\INSTANCE-ReplicationTest-Pub132_ReplicationTa-Tigereye\INSTANCE ) has been queued because the maximum number of working threads (200) are already running.  This job will be executed as soon as one of the working thread finishes execution.

MaxWorkerThreads for SQL Agent subsystem

There is also a MaxWorkerThreads for each SQLAgent subsystem. To increase the MaxWorkerThreads for a subsystem update the msdb.dbo.syssubsystems table and stop and start SQLAgent.
If you hit this limitation of MaxWorkerThreads for SQLAgent subsystem. you will see a message [251].

There is also a MaxWorkerThreads for each SQLAgent subsystem. To increase the MaxWorkerThreads for a subsystem update the msdb.dbo.syssubsystems table and stop and start SQLAgent. If you hit this limitation you will see a message [251].

+ [251] Step 1 of job Tigereye\INSTANCE-Replicationxxxx_ReplicationTab-xxxx\INSTANCE-100 is being queued for the TSQL subsystem

 

Subsystems

SQL 2000 Agent Subsystem settings are stored in the Registry.

SQL 2005/2008 Agent Subsystem settings are stored in MSDB. We can use below query to get max_worker_threads of each subsystem.

select subsystem,
       right(subsystem_dll,20) as 'Agent DLL',
       right(agent_exe,20) as 'Agent Exe',
       max_worker_threads
from msdb.dbo.syssubsystems

 

Status of job would be "Waiting for worker thread" when concurrent jobs reaches the max_worker_thread limitation of agent subsystem.

Thank you,

Karthick P.K |Technical Lead | Microsoft SQL Server Support |

My Facebook Page |My Site| Blog space| Twitter

Comments

  • Anonymous
    September 20, 2012
    Excellent post

  • Anonymous
    May 04, 2013
    Thanks, very useful