I'm looking for a setting (other than schedules shifting) which could limit the number of jobs running simultaneously. I have 844 jobs in my SQL Agent (SQL Server 2017 Enterprise). Most of them needs few minutes to complete, there are about 190 jobs that needs half of hour. All of them are scheduled to run every hour. If they run all together, I can't fit total duration time below 60 minutes. Is there any setting that could queue 744 jobs until one from those 844 finishes and SQL Agent would start the next job waiting in the queue?
4
-
3This feels like an X/Y problem - something this complex would likely be much better handled with SSIS packages or similar. That being said, what are the jobs doing? Is the code running slow? By doing what you're doing aren't you going to slow things down even more?– George.PalaciosCommented May 10, 2019 at 11:42
-
Segregate and put a few in same job with steps. This will make it possible to run a step after the first completes.– Ramakant DadhichiCommented May 10, 2019 at 11:42
-
They are running SSIS packages. Hence the packages are pretty heavy (crashing Vistual Studio SSDT due to out of memory), it's hard to touch them. What are they doing - syncing the data from many sources into one place. I may want to do as @RamakantDadhichi wrote.– bigderCommented May 10, 2019 at 12:03
-
You may be able to tap into Resource Governor since you're using Enterprise Edition. The answer to this DBA.SE question provides a pretty good rundown of what's happening under the covers: dba.stackexchange.com/questions/184625/…– John EisbrenerCommented May 10, 2019 at 16:19
Add a comment
|
1 Answer
This is best done outside of SQL Agent. Honestly, at 800+ jobs running every hour you should have moved to a different method long before now.
My preference for this would be to create a single master SSIS package that reads in a list of jobs that need to be run and then starts up the first X in the list, then sleeps until the number of running jobs is below threshold, then starts up the next X in the list. You could randomize the list or sort it, as you choose. I've done something similar to this in my environment where I needed to make sure that certain classes of packages (client imports) were done one at a time.
The less dynamic approach would be to just break the list of jobs that need to be run into however many simultaneous you want running. Each of those would have a list of steps that call the actual job that needs running. It takes less fiddling about with timing, sleeps and querying active jobs but is less dynamic (for example, changing the number of max simultaneous threads will take work.
But in any case, I would leave the jobs as they are now, just remove/disable their schedules. Then whatever solution you want can just call the sp_start_job procedure. This lets you still easily run any job you want out of cycle and you see independent reports of success/failure.
answered May 10, 2019 at 12:57
