SQL Server Agent Powershell Subsystem with only 2 worker threads

Question

SQL Server Agent Powershell Subsystem with only 2 worker threads

Alexander G.

SSCrazy

Points: 2705
More actions
February 1, 2012 at 6:56 am

#267483

My Sql Server Agent can only run two powershell jobsteps at one time which apparently is caused by the max_worker_threads setting:
[font="Courier New"]SELECT subsystem, max_worker_threads FROM msdb.dbo.syssubsystems
subsystemmax_worker_threads
TSQL240
ActiveScripting120
CmdExec120
Snapshot1200
LogReader300
Distribution1200
Merge1200
QueueReader1200
ANALYSISQUERY1200
ANALYSISCOMMAND1200
SSIS1200
PowerShell2[/font]
Why are so little PowerShell threads allowed compared to other subsystems? Is it save to set the value to say 10?

Viewing 6 posts - 1 through 5 (of 5 total)

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

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

No reason why you cant increase it.

I've got it increased to 1/4 of my available cores on all servers. I dont expect to ever use that many but it allows more flexibility.

Jeffrey.Simmons SSC Enthusiast Points: 126 More actions · Answer 2

I have been trying to change this in SQL 2012. Every time I change the value for PowerShell in the syssubsystems table when I restart the SQL agent it is set back to 2. SQL 2008R2 persisted this change. Anyone been able to change it in 2012?

bgreco SSC Enthusiast Points: 114 More actions · Answer 3

I just noticed this 2008-2012 difference myself when looking at the max_worker_threads for the TSQL subsystem.

I think sp_verify_subsystems must run whenever SQL agent starts. In 2008, it only inserts into the syssubsystems table if there are entries missing, so your modifications persist across restarts. In 2012, it truncates the table and re-inserts every time!

All of the subsystems except Powershell are set to be multiples of the server's CPU count. The Powershell subsystem is hardcoded to 2, go figure. :hehe:

trayalacheruvu SSC Eights! Points: 965 More actions · Answer 4

Hi Thanks for your feedback. On this i am having similar issue and i am trying change the CmdExec Worker thread value , but after restarting the SQL Agent the value is rolling back. As you said this CmdExec is multiplies of CPU. Can you please let me know what is digit we need multiply for this subsystem? do we have any documentation on this from MS?

Thanks for your reply in advance.

Regards,

Thulasi.

wannabe1 SSCommitted Points: 1960 More actions · Answer 5

edit sp_verify_subsystems in msdb. This inserts the value to subsystems table by multiplying an arbitrary number to the no.of processors. you can manipulate this number to get the desired worker threads

For powershell, just change 2 to the number you want

INSERT syssubsystems

VALUES

(

12, N'PowerShell', 14698, @InstRootPath + N'SQLPOWERSHELLSS.DLL', @SQLPSPath, N'PowerShellStart',N'PowerShellEvent',N'PowerShellStop',2

)