Adding a customer SQL Agent subsystem

Hamilton, Chuck 1 Reputation point
2023-07-20T16:22:46.1733333+00:00

Has anyone ever tried to create a custom subsystem for the SQL Agent? I was thinking about trying it wondering if this would work?

The goal is to create a duplicate of the T-SQL subsystem that limits the # of concurrently running jobs to 3.

The problem - We have about 50 T-SQL jobs that all launch at the same time every 5 minutes and overwhelm the server's CPU. The traditional approach might be to stagger the jobs but I was wondering if I can just add a new row to MSDB's syssubsystems table that's a copy of the T-SQL one. It would have a unique subsystem_id and a new name something like "T-SQL limited" and I'd set the max_worker_threads to 3. I'd also give it a new description_id along with a corresponding new row in sys.messages (using sp_addmessage). All 50 jobs would be changed to use this new subsystem. This way all 50 could launch at once, but they'd be intentionally queued so that only 3 could run concurrently.

I realize I could also add CPU but I think I would not need it if the starting of the jobs was better controlled.

Is this a hare-brained idea or am I on to something?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,477 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 116.9K Reputation points MVP
    2023-07-20T22:13:23.9866667+00:00

    Have you thought of using Resource Governor? (Which is only available in Enterprise Edition.)


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.