August 15, 2012 at 5:35 am
Hi all -
We have a real-time system for checking the status of lone workers, and I currently create a SQL Agent job for each worker, set to run according to when the next check time is due (what happens then is not relevant to this enquiry). We have been happily using this with 50 or so workers, but wish to scale up to a possible maximum of several thousand.
I started to get uneasy about creating that many jobs, but couldn't find any hard information about limits imposed on job creation. I did find that only 60 jobs can be started in any one minute, which may yet see this approach ruled out, but I wondered if anyone has any words of wisdom about having many, many jobs on a server.
Thanks for all comments,
Bob
August 15, 2012 at 5:50 am
I'm not sure if there's an absolute limit. I can see a few suggestions that there may have been in SQL 2000, but nothing concrete.
I would strongly reconsider this design though. It would be much more scalable to maintain a table with the schedules for all workers in it, then run a single Agent job every minute or so to check for any schedules that need processing at that time.
August 15, 2012 at 6:48 am
HowardW (8/15/2012)
I'm not sure if there's an absolute limit. I can see a few suggestions that there may have been in SQL 2000, but nothing concrete.I would strongly reconsider this design though. It would be much more scalable to maintain a table with the schedules for all workers in it, then run a single Agent job every minute or so to check for any schedules that need processing at that time.
Jobs are all driven from msdb so the only limit imposed is likely to be maximum database size, but I doubt MS ever considered testing those billions of jobs. You will likely start getting issues with the sql agent service way before you reach the max db size as this will be running a timer loop and the more jobs it has to manage the bigger potential delay from scheduled start time.
August 16, 2012 at 12:50 pm
This is inverted from the model I'm used to for a scenario like this. Usually either the worker, or a local monitoring software on the worker, will 'call in' to SQL (or preferably, a messaging queue) to deliver a status, instead of a polling from the SQL Server. In some cases specialized software goes out and does it for more maintainable customization. This allows for each component to be brought online/offline without server maintenance every time you stand up a new whatzit.
To the original question, no, there's no hard limit, but as mentioned, you'll probably run into an eventual optimization issue for the agent loop. I don't think a few thousand will get you there however unless you're running 10-20 unique schedules (or a few hundred) on each.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply