microsoft.public.sqlserver.misc,microsoft.public.sqlserver.datawarehouse,microsoft.public.sqlserver

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

  1. av_frco

    av_frco Guest

    Hello,

    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)

    Summary:

    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,
    Frank.
     
    av_frco, Nov 19, 2006
    #1
    1. Advertisements

  2. av_frco

    John Bell Guest

    Hi

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

    John
     
    John Bell, Nov 20, 2006
    #2
    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.