SQL Server Performance

Urgent - ETL job taking more time

Discussion in 'Performance Tuning for DBAs' started by pushpakn, Feb 28, 2006.

  1. pushpakn New Member

    Hi,

    I have added around 520 indexes to 200 tables in production. I have done this yesterday, and after that today, the performance of the application as well as reports has increased. But there is an ETL job which runs nightly and does a incremental insert into the database to 200 tables.
    This has taken 4 hours rather than usual 1 hour.
    My question is that : Is this a permanent increase in the ETL runtime or is this just for today. If it is just for today then why does it happen and also if it permanent, then what is the best way to reduce the time for the ETL?

    One thing we can do is to drop indexes -- run the ETL -- create the indexes.
    But is this feasible, considering this is a live database which is accessed hugely.

    Comments are appreciated.

    Regards,
    Popo.
  2. satya Moderator

  3. mmarovic Active Member

    quote:Is this a permanent increase in the ETL runtime or is this just for today. If it is just for today then why does it happen and also if it permanent, then what is the best way to reduce the time for the ETL?
    It is probably permanent.
    If you the db is accessed during etl you may consider selectively dropping indexes and creating them later. Since your application worked somehow before you added all that indexes you may drop some indexes you have just creted from larger tables and create them after. That way you may have worst response time during etl but since etl would take less time, response time would significantly increase after if finishes earlier.

Share This Page