Post reply

Maximum number of concurrent SQL Server Agent

  • April 29, 2016 at 2:00 am

    #326666

    Hi,

    I am trying to execute multiple jobs (like 2 jobs for each user).

    Is there a upper limit on the number of jobs which can run concurrently ?

    These jobs will perform SELECT,INSERT,DELETE on the tables.

    Thanks!!

  • April 29, 2016 at 3:12 am

    #1875496

    Why do you want to do SELECT/INSERT/DELETE via a job? What is wrong with a series of stored procedures?

    Jez

  • April 29, 2016 at 3:50 am

    #1875500

    No, there's no limit, but the more jobs you attempt to run at the same time, the more likely you are to experience concurrency issues (blocking and deadlocks) and contention for resources such as disk, memory and CPU. Make sure that your queries are written to be as efficient as possible and that the tables being indexed are optimally indexed.

    John

  • April 29, 2016 at 3:59 am

    #1875504

    way back in sql 2000 there were registry settings for the max number of concurrent threads sql agent could run. these were broken down into subcategories... replication, job agent, maint plans etc.

    I have no idea if these still exist, but it's worth looking at.

    also the sql agent scheduler only runs 1 job per second, so if you have more than 60 jobs scheduled to start in the same minute, then only 60 of them will start, the rest will start in the next minute...

    MVDBA

  • April 29, 2016 at 4:01 am

    #1875505

    start by looking here

    https://blogs.msdn.microsoft.com/karthick_pk/2011/02/01/sql-agent-maxworkerthreads-and-agent-subsystem/

    MVDBA

  • April 29, 2016 at 5:28 am

    #1875529

    The controller for these jobs has other tasks to perform apart from creating and starting jobs.

    This is the specific requirement as jobs can run asynchronously.

  • April 29, 2016 at 6:00 am

    #1875539

    Have you thought about using Broker Services?

    Jez

  • April 29, 2016 at 7:58 am

    #1875584

    Your limits are the same as they would be if it was a client application calling the database. INSERT/UPDATE/DELETE will cause blocking. SELECT with poor or improper code can lead to scans. All this needs memory, cpu and I/O and you can bottleneck on those resources depending on what you're doing. There's nothing magic, good or bad, about calling from SQL Agent.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply