Adjusting the maximum number of worker threads per SQL Server Agent’s subsystem when processors are added or removed from the machine after the instance of SQL Server was installed

  • Article

Inside MSDB, there exists a system table named dbo.subsystems which is populated the first time SQL Server Agent services runs.

Every time SQL Server Agent starts up, among the several things it does, it interrogates the contents of that table to find out what subsystems are enabled and how they are configured. To do so, it executes this:

EXECUTE msdb.dbo.sp_enum_sqlagent_subsystems_internal @syssubsytems_refresh_needed = 1

Since the implementation of that stored procedure is not obfuscated in any way, you can go and find for yourself what it does in detail. But, basically, it checks if the table is empty. If it is, it populates it with the most current configuration information associated to each subsystem. Part of a subsystem’s configuration is the maximum number of worker threads that SQL Server Agent is allowed to instantiate to concurrently execute steps of that particular subsystem.

That maximum number of threads was calculated only once – when the table was populated the first time the service started – and only then, just because since then the table has never been found empty again.

So, especially if at any given point in time, after having installed SQL Server, you decide to significantly multiply or reduce the number of processors available in that machine, you may want to readjust those maximum limits.

To do so, it is as easy as following these steps:

  1. Stop SQL Server Agent.
  2. Delete all rows in msdb.dbo.syssubsystems from the instance of SQL Server to which that instance of the Agent is part of.
  3. Restart SQL Server Agent.