Adding a customer SQL Agent subsystem

Question

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?

Answer 1

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