Migration SQL2000-2005 Performanceleak

[Jan Waiz]

Hi All,

when migrating from SQL2000 to SQL2005 we have a massive performanceleak
with a view used to fill a cube.

The View needs nearly 1 hour in SQL2000 and (unchanged) in SQL2005: 4-1/2
hours !

We try to optimize the view searching for indexfailures and so on and used
the profiler - but without success.

Some one out there, who has a tip or an idea what happens and where we can
focus our work?

Many thanks in advance

Regards
Jan Waiz

[Todd C]

Jan:

I'm no expert in performance stuff like this, so this bit of free advice may
be worth exactly what you have paid for it ;-)

Have you looked at the fragmentation of the indexes, or tried re-building
them?

When you say it went from 1 hour to 4-1/2, are you using the same
methodology for both tests? I only ask because you posted the issue in the
DTS forumn and thought possibly you are now using SSIS for the data movement.

Best of luck,
=====
Todd C

Post by Jan Waiz
Hi All,
when migrating from SQL2000 to SQL2005 we have a massive performanceleak
with a view used to fill a cube.
The View needs nearly 1 hour in SQL2000 and (unchanged) in SQL2005: 4-1/2
hours !
We try to optimize the view searching for indexfailures and so on and used
the profiler - but without success.
Some one out there, who has a tip or an idea what happens and where we can
focus our work?
Many thanks in advance
Regards
Jan Waiz

[Jan Waiz]

Ho Todd,

tx4r

In both cases we test it by calling the Mainview from within the SQL-Server
Management Studio via a statment like:

SELECT * FROM ThisView

The Mainview and the Subviews which are used are still copied from SQL 2000
to SQL 2005. Also the Tables and the Data inside the Tables. I checked this
again and again by creating a new database and creating the tables, views
and the data via the same script.

its magic... *sick*

Regards
Jan Waiz

Post by Todd C
I'm no expert in performance stuff like this, so this bit of free advice may
be worth exactly what you have paid for it ;-)
Have you looked at the fragmentation of the indexes, or tried re-building
them?
When you say it went from 1 hour to 4-1/2, are you using the same
methodology for both tests? I only ask because you posted the issue in the
DTS forumn and thought possibly you are now using SSIS for the data movement.
Best of luck,
=====
Todd C

Post by Jan Waiz
Hi All,
when migrating from SQL2000 to SQL2005 we have a massive performanceleak
with a view used to fill a cube.
The View needs nearly 1 hour in SQL2000 and (unchanged) in SQL2005: 4-1/2
hours !
We try to optimize the view searching for indexfailures and so on and used
the profiler - but without success.
Some one out there, who has a tip or an idea what happens and where we can
focus our work?
Many thanks in advance
Regards
Jan Waiz