Discussion in 'microsoft.public.sqlserver.server' started by av_frco, Nov 19, 2006.

  1. av_frco

    av_frco Guest


    We have been facing a problem for almost two months. All of a sudden,
    we got a 300% performance degradation in our ETL from our staging
    database to our datawarehouse, for some tables.

    Here's the setup on the production server:

    - We have a server running Microsoft SQL 2000 SP4 with SAN disks, 4
    processor, 16 GB RAM, using Microsoft Windows Server 2003.
    - We are using Informatica PowerCenter for our ETL from the staging to
    the datawarehouse
    - SQL Analysis Services are running for the buildings of the cubes
    - Two problematic target tables have about 10 millions rows. Another
    one has 600 millions rows.
    - There are no user using the database when the ETL runs, this is the
    only process running (it runs during the night)


    For months, everything was going well, the ETL session would complete
    in 65-75. Suddenly, we got very poor performance and the session takes
    anywhere between 3-20 hours. This is a big problem because we can't run
    the ETL during the day when users have to access the data.

    Our tests indicates that this is not a I/O related problem. We narrowed
    the problem to about three target tables.

    Here's what we tried:

    - We created worket to write to a flat file instead of SQL Server:
    performance is still poor.
    - We ran our ETL for the identified tables in isolation: performance is
    still poor.
    - We rebuilded the production environment (SQL Server, Informatica,
    Analysis Services): no changes.
    - We tried different configuration in informatica such as varying cache
    sizes but performance remained poor.
    - We then decided to purge some data. At first performance came back to
    normal but 3 days later the performance problem returned. We are now
    purging data everyday and do not run the problematic ETLs in order to
    keep the production server running.
    - We built a test environment on another machine but still the ETL
    takes forever

    We are now trying to determine the best plan possible to find a
    solution to this problem. Any insights on what could be the problem or
    any idea on how to narrow it down would be appreciated.

    Thanks a lot,
    av_frco, Nov 19, 2006
    1. Advertisements

  2. av_frco

    John Bell Guest


    I assume you have run SQL profiler to see how the database is being queries
    and then looked at the execution plans?

    John Bell, Nov 20, 2006
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.