locked
Execution time doubled for job in Sql server RRS feed

  • Question

  • i have a job in running on DI  that gets the data from Oracle database and puts it into sql server database

    The model is designed using a business objects data integrator which has different dataflows

    the execution time of the job suddenly increased double in time

    Can some one please tell if there is any way to check why execution time doubled

    Can i use a trace file to check this in database engine tuning advisor?




    • Edited by LuckyDba Monday, September 10, 2012 9:18 PM
    Monday, September 10, 2012 1:51 PM

Answers

  • have you checked :

    • check if something new schduled on server, like some new jobs at same time that may be conflicting..on both SQL & oracle
    • check for blocking at same time.....on both SQL & oracle
    • check the amount of data in table, may be data increased due to some bulk insert or anything else

    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    • Marked as answer by LuckyDba Wednesday, September 26, 2012 1:42 PM
    Wednesday, September 12, 2012 9:02 PM
  • Check & sahre some more details

    1) execution time is double for each run or that was only for 1 time

    2) Yes you need check to blocking on regular basis.....normally i schedule a blocking check in each 15 min.............on both SQL & ORACLE

    3) check the fragmentation level of tables ........on both ORACLE & SQL

    4) Share the size & no. of records of table..................on both ORACLE & SQL

    5) Data in table is append on daily basis or table got truncated & get new data everytime...............on SQL server

    7) Are stats are updating on regular basis.........on both ORACLE & SQL

    8) amount of data populated each time

    9) analysis the network utilization for last some time at the time your job run (network from ORACLE to SQL)


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!


    • Edited by RohitGarg Thursday, September 13, 2012 6:04 PM
    • Marked as answer by LuckyDba Wednesday, September 26, 2012 1:42 PM
    Thursday, September 13, 2012 6:04 PM
  • But try below one

    • Rebuild the indexes on both SQL server & oracle (expect the table at sql server got truncated & got new data every time) for all tables using in this process
    • Update the sattisitcs of both SQL & oracle afte index rebuild
    • after above steps, try to run your process again

    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by LuckyDba Wednesday, September 26, 2012 1:43 PM
    Wednesday, September 19, 2012 10:29 PM
  • Convined with rohit...


    Thanks

    • Marked as answer by LuckyDba Wednesday, September 26, 2012 1:42 PM
    Saturday, September 22, 2012 5:08 PM

All replies

  • You need to define the "data integrator" that you are talking about; there is hardly enough information here to make a reasonable assessment.
    Monday, September 10, 2012 2:49 PM
  • Sorry about that

    its a business objects data integrator

    Monday, September 10, 2012 3:49 PM
    • check if something new schduled on server, like some new jobs at same time that may be conflicting
    • check for blocking at same time
    • check the amount of data in table, may be data increased due to some bulk insert or anything else


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!


    • Edited by RohitGarg Tuesday, September 11, 2012 1:46 PM
    Tuesday, September 11, 2012 1:46 PM
  • >Can some one please tell if there is any way to check why execution time doubled

    You could make billions of dollars if you invent a tool for it.

    Can you post the query(ies)?

    You can create a trace:

    http://www.sqlusa.com/bestpractices/createtrace/

    For more assistance visit the SSIS forum, this is database design.

    SSIS: SQL Server Integration Services


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Wednesday, September 12, 2012 8:48 PM
    Wednesday, September 12, 2012 10:59 AM
  • We are not using queries

    there is this tool data integrator which has all the data objects to move data from oracle database to SQl server database

    so i would like to know is its because of performance issue from the sql server side?

    can profiler give me any results if i run it for this particular user?

    Wednesday, September 12, 2012 7:21 PM
  • have you checked :

    • check if something new schduled on server, like some new jobs at same time that may be conflicting..on both SQL & oracle
    • check for blocking at same time.....on both SQL & oracle
    • check the amount of data in table, may be data increased due to some bulk insert or anything else

    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    • Marked as answer by LuckyDba Wednesday, September 26, 2012 1:42 PM
    Wednesday, September 12, 2012 9:02 PM
  • yes rohit

    i checked for any jobs that are running at that the tym of  the datamigration job but i dont see any jobs running at that time

    i have also checked for blocking but there were no blocking

    the amount of data has always been almost the same since the beginning of the migration from oracle DB to Sqlserver DB

    do i need to keep checking for blocking from start of job till end?

    i just checked for blocking once in between whn the job was running

    The job has not been scheduled on Sql server agent its from Data Integrator a business objects tool

    Thank you rohit

    • Edited by LuckyDba Thursday, September 13, 2012 1:51 PM
    Thursday, September 13, 2012 1:49 PM
  • Check & sahre some more details

    1) execution time is double for each run or that was only for 1 time

    2) Yes you need check to blocking on regular basis.....normally i schedule a blocking check in each 15 min.............on both SQL & ORACLE

    3) check the fragmentation level of tables ........on both ORACLE & SQL

    4) Share the size & no. of records of table..................on both ORACLE & SQL

    5) Data in table is append on daily basis or table got truncated & get new data everytime...............on SQL server

    7) Are stats are updating on regular basis.........on both ORACLE & SQL

    8) amount of data populated each time

    9) analysis the network utilization for last some time at the time your job run (network from ORACLE to SQL)


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!


    • Edited by RohitGarg Thursday, September 13, 2012 6:04 PM
    • Marked as answer by LuckyDba Wednesday, September 26, 2012 1:42 PM
    Thursday, September 13, 2012 6:04 PM
  • But try below one

    • Rebuild the indexes on both SQL server & oracle (expect the table at sql server got truncated & got new data every time) for all tables using in this process
    • Update the sattisitcs of both SQL & oracle afte index rebuild
    • after above steps, try to run your process again

    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by LuckyDba Wednesday, September 26, 2012 1:43 PM
    Wednesday, September 19, 2012 10:29 PM
  • Convined with rohit...


    Thanks

    • Marked as answer by LuckyDba Wednesday, September 26, 2012 1:42 PM
    Saturday, September 22, 2012 5:08 PM