MSSQLWIKI

Karthick P.K on SQL Server

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

select subsystem, 

 

      right(subsystem_dll,20) as 'Agent DLL', 

 

      right(agent_exe,20) as 'Agent Exe', 

 

      max_worker_threads 

 

from msdb.dbo.syssubsystems

Subsystem AgentDLL AgentEXE Max_worker_threads
TSQL [Internal] [Internal] 80
ActiveScripting QL\binn\SQLATXSS.DLL NULL 40
CmdExec QL\binn\SQLCMDSS.DLL NULL 40
Snapshot QL\binn\SQLREPSS.DLL 100\COM\SNAPSHOT.EXE 400
LogReader QL\binn\SQLREPSS.DLL \100\COM\logread.exe 100
Distribution QL\binn\SQLREPSS.DLL \100\COM\DISTRIB.EXE 400
Merge QL\binn\SQLREPSS.DLL 100\COM\REPLMERG.EXE 400
QueueReader QL\binn\SQLREPSS.dll \100\COM\qrdrsvc.exe 400
ANALYSISQUERY L\binn\SQLOLAPSS.DLL NULL 400
ANALYSISCOMMAND L\binn\SQLOLAPSS.DLL NULL 400
SSIS QL\binn\SQLDTSSS.DLL \DTS\Binn\DTExec.exe 400
PowerShell \SQLPOWERSHELLSS.DLL Tools\Binn\SQLPS.exe 2

9 Responses to “SQL Agent MaxWorkerThreads and Agent subsystem”

  1. Once i originally commented I clicked the -Notify me when new comments are added – checkbox now each time a comment is added I receive four e-mail with the exact same comment. Can there be any way you are able to take out me from that service? Thanks!

  2. Hello, i think that i saw you visited my web site thus i came to “return the favor?I’m attempting to find things to improve my site!I suppose its ok to use some of your ideas!!

  3. Sorry for the huge review, but I’m really loving the new Zune, and hope this, as well as the excellent reviews some other people have written, will help you decide if it’s the right choice for you.

  4. You made some good points there. I looked on the internet for the subject and found most guys will go along with with your blog.

  5. You are a very bright individual!

  6. thanks

  7. Alexander said

    Is there a specific reason for PowerShell subsystem having max worker threads=2? A lot of my jobsteps use powershell subsystem so only two of them can run (even if I have 12 cores…) Can I safely set the max worker threads to say 50?

  8. Hosea said

    Right away I am ready to do my breakfast, later than having my
    breakfast coming yet again to read further news.

    click here
    click here
    click here
    click here
    click here
    click here
    click here
    click here
    click here
    click here
    click here
    click here
    click here

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,136 other followers

%d bloggers like this: