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

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.Hi
I assume you have run SQL profiler to see how the database is being queries
and then looked at the execution plans?
John
"av_frco@.hotmail.com" wrote:
> 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.
>

microsoft.public.sqlserver.misc,microsoft.public.s qlserver.datawarehouse,microsoft.public

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.
Hi
I assume you have run SQL profiler to see how the database is being queries
and then looked at the execution plans?
John
"av_frco@.hotmail.com" wrote:

> 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.
>