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:
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? :-)
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 for the clarification. Yes, I do see values for max_worker_threads reflecting the cores Windows can see, with no correleation to what the SQL instance can use. So on a 40CPU box an instance with affinity of 1-2 CPUs will try to spawn 800 worker theads via SQLAgent. Strange isn't it.
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.