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 |
MSSQLWIKI 
beats by dre said
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!
Beats by Dre Pro Sale said
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!!
Bertram Crowthers said
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.
Beats by Dre Studio UK said
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.
Beats By Dre New Style UK said
You are a very bright individual!
auto responder/autoresponders said
thanks
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?
Karthick P.K said
No, You can safely change as long as there is no resource bottleneck.
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
FrankF said
This was very helpful. I migrated my distributor and discovered about a dozen distribution agent jobs were just sitting… not idle, not running, the column was blank. This fixed me up. Thanks!