Share via

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.

Comments

  • Anonymous
    January 22, 2013
    Nacho, I have tried this on a few SQL2008R2 and SQL2008 instances and has made no difference! All instances have had their CPU affinity changed, yet the repopulated msdb.dbo.syssubsystems tables contain identical data. I would have thought that max_worker_threads would drop after deselecting half the CPUs from the affinity mask. Any ideas? :-)

  • Anonymous
    January 23, 2013
    Hi John, Changing the affinity mask for an instance of the Database Engine doesn't have any effect on the SQL Server Agent associated to that same instance. What I explain in this post is only applicable when you add or remove CPUs to the machine where the instance of SQL is installed. SQL Server Agent doesn't provide a built in mechanism that would allow you to affinitize the worker threads of the different subsystems to a subset of the cores in the hosting machine. Hope this clarifies and responds your question. Thanks, Nacho

  • Anonymous
    January 23, 2013
    The comment has been removed

  • Anonymous
    January 24, 2013
    Indeed John. I've filed a DCR titled "Provide a mechanism for the user to be able to affinitize and size to their convenience the maximum number of worker threads per subsystem" so that the product group takes this into consideration for future releases. Many thanks for your feedback.