locked
Sql Server agent Jobs running RRS feed

  • Question

  • Hi All,

    We have a scenario where we have to run 30 pkgs at same second. Each pkg is designed to fetch 60 lakh per day records and load it into Oracle/OLEDB destination. For which we have created 30 SQL agent jobs for each pkg running every 5 mins 24/7. We want to test running with option(1) these 30 jobs on single sql server instance and option(2) 10 jobs each on 3 sql server instances. Can someone please help me out to understand which would give me the better results.

    a) The main advantage of multiple sql server instance scenario when compared with single sql server instance?
    b) How much does the hardware configurations impact on the performance?

    Thanks in Advance....

    Friday, February 27, 2015 2:02 PM

Answers

  • There is also another issue that can happen when running lots of jobs in parallel. There is a max worker thread setting which when crossed jobs will get failed

    see

    http://visakhm.blogspot.in/2011/06/sql-server-agent-job-issues-2.html

    One question here is why does this have to be in separate jobs? Is there a way where they can be arranged as steps within job? Or do you want them to execute in parallel? I hope there's no dependency among them in that case.

    Also if data volume is a concern consider doing a batch job logic for them so that each time required batches will get executed in parallel which will not cause too much issues with buffer space being getting filled easily


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Monday, March 2, 2015 8:57 AM

All replies

  • I am afraid your approach is not good. You are going to get exposed to race conditions at the very least.

    I understand you want to arrive to distributed computing, but you are not using the correct tools.

    Please clarify "60 lakh per day".

    And the the a) bullet: are the instances on different machines?

    b) a non-linear but direct relation.

    Again so far your approach does not look sound at least to me.


    Arthur

    MyBlog


    Twitter

    Friday, February 27, 2015 2:39 PM
  • 1. Exposed to race conditions means ?

    2. What you mean distributing computing? Can't the SQL Server handle this much load ?

    3. Yes, 60 lakh records per day.

    4. Multiple Instances on the same machine. The jobs might scale up to 300 jobs.

    So, please suggest whether multiple instances would help the approach in scaling up ? Or the Single instance can handle all the 300 jobs at same second?

    Please advice which approach would be best and why?

    Monday, March 2, 2015 6:20 AM
  • Please clarify "60 lakh per day".

    Arthur

    MyBlog


    Twitter

    60 lakh = 0.6 million  ( 1 lakh/lac = 0.1 million)

    @BHVS: Please use million/billion units as these are universally known units.


    -Vaibhav Chaudhari

    Monday, March 2, 2015 6:36 AM
  • There is also another issue that can happen when running lots of jobs in parallel. There is a max worker thread setting which when crossed jobs will get failed

    see

    http://visakhm.blogspot.in/2011/06/sql-server-agent-job-issues-2.html

    One question here is why does this have to be in separate jobs? Is there a way where they can be arranged as steps within job? Or do you want them to execute in parallel? I hope there's no dependency among them in that case.

    Also if data volume is a concern consider doing a batch job logic for them so that each time required batches will get executed in parallel which will not cause too much issues with buffer space being getting filled easily


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Monday, March 2, 2015 8:57 AM
  • 1. Exposed to race conditions means ?

    2. What you mean distributing computing? Can't the SQL Server handle this much load ?

    3. Yes, 60 lakh records per day.

    4. Multiple Instances on the same machine. The jobs might scale up to 300 jobs.

    So, please suggest whether multiple instances would help the approach in scaling up ? Or the Single instance can handle all the 300 jobs at same second?

    Please advice which approach would be best and why?

    Re #1: means it will step on each other

    #2: is when you take a chunk of data and process each separately at the same time. SQL Server or not will use the resources available on the machine. And they are not elastic.

    #3: So thanks @Vaibhav it is 600K records to process so per 5 min it is 300 records which not too many for even one SSIS package on a commodity server. Question though is how long is the record. And what needs to be done to "process", if it takes longer than 5 min - boom you are trouble with your approach (#1 happens)

    Lastly, how do you know it will scale?

    To answer you need

    1) Provide with technical specs of your prod machine

    2) Do a proof of concept

    3) Profile a load to see where improvements can be done because in ETL it is common to get more and more data as the time goes by. I also fail to understand how the concurrently running packages will understand what data not to process because another package grabbed it.


    Arthur

    MyBlog


    Twitter

    Tuesday, March 3, 2015 12:56 AM