Changing Compatibility Level on an Upgraded SQL Server 2000 db to 2005 causing delete to b

I have just upgraded my SQL Server 2000 to SQL Server 2005 and from
what I understand the upgrade will do the database conversions for you.
After the install I tried to delete a row from my Item table which is a
foreign key to about 10 other tables. A simple
Delete Item Where ItemID = 888
In SQL Server 2000 it takes about 9secs. In SQL Server 2005 i have yet
to let it run successfully, i stop it after 5mins.
After the upgrade I changed the Compatibility Level to SQL Server 2005
(90) under Database Properties -> Options
After I changed the Compatibiliity Level back to SQL Server 2000 (80)
the query ran in approx 17 secs.
The Item table is about 8000 rows and the size of the tables
referencing the Item table varies. But why would changing the
Compatibility Level cause such a difference in performance? Did I miss
a step in upgrading? From what I understood, changing the Compatibility
Level just allows you to use new SQL Server 2005 features like MARS,
etc...
Please help.
thanks
benblan
UPDATE STATISTICS
REBUILD INDEXES
"blam" <bendlam@.gmail.com> wrote in message
news:1138810049.298299.209110@.g43g2000cwa.googlegroups.com...
>I have just upgraded my SQL Server 2000 to SQL Server 2005 and from
> what I understand the upgrade will do the database conversions for you.
> After the install I tried to delete a row from my Item table which is a
> foreign key to about 10 other tables. A simple
> Delete Item Where ItemID = 888
> In SQL Server 2000 it takes about 9secs. In SQL Server 2005 i have yet
> to let it run successfully, i stop it after 5mins.
> After the upgrade I changed the Compatibility Level to SQL Server 2005
> (90) under Database Properties -> Options
> After I changed the Compatibiliity Level back to SQL Server 2000 (80)
> the query ran in approx 17 secs.
> The Item table is about 8000 rows and the size of the tables
> referencing the Item table varies. But why would changing the
> Compatibility Level cause such a difference in performance? Did I miss
> a step in upgrading? From what I understood, changing the Compatibility
> Level just allows you to use new SQL Server 2005 features like MARS,
> etc...
> Please help.
> thanks
> ben
>|||thanks for the reply,
I did all that last night, rebuilt the indexes, updated the statistics,
etc...this morning the query still didn't run, i had to stop it after
5mins. Then all I did was change the compatibility level to SQL Server
2000 (80) then reran the query and it took 17secs.
Any other suggestions?|||blam wrote:
> thanks for the reply,
> I did all that last night, rebuilt the indexes, updated the statistics,
> etc...this morning the query still didn't run, i had to stop it after
> 5mins. Then all I did was change the compatibility level to SQL Server
> 2000 (80) then reran the query and it took 17secs.
> Any other suggestions?
Check the query plan with both compatibility levels. That may give a clue.|||Can you compare the execution plan with compatibility level 80 with the
excution plan with compatibility level 90
I found different values in the execution plan when i change the
compatibility level between 70 / 80.|||You may need to change your queries/stored procs. After we migrated to SQL
2005, some of the stored procedures slowed down to a crawl, even after
updating the stats and rebuilding the indexes. Due to changes in the database
engine from version to version (and even from service upgrades), the query
plans may change as well. We had do minor changes to the queries and sps in
order to get it back to the way it was performing on SQL 2000.
"gshadow1" wrote:
> Can you compare the execution plan with compatibility level 80 with the
> excution plan with compatibility level 90
> I found different values in the execution plan when i change the
> compatibility level between 70 / 80.
>
>

Changing Collation SQL 2000 sp4

Hello
We just upgraded to SQL server 2000 sp3a from sp4 . Could the
collation
change upgrading to SP4 (from SQL_Latin1_General_CP1_CS_AS to
SQL_Latin1_General_CP1_CI_AS. Or can that only be changed on the
initial installation.
ThanksHi
"jsully79" wrote:

> Hello
> We just upgraded to SQL server 2000 sp3a from sp4 . Could the
> collation
> change upgrading to SP4 (from SQL_Latin1_General_CP1_CS_AS to
> SQL_Latin1_General_CP1_CI_AS. Or can that only be changed on the
> initial installation.
> Thanks
I have not know the collation change when applying the service pack. To do
for the whole system would have required alot of work. Are you asking about
a
specific database/column/setting?
John

Changing Collation SQL 2000 sp4

Hello
We just upgraded to SQL server 2000 sp3a from sp4 . Could the
collation
change upgrading to SP4 (from SQL_Latin1_General_CP1_CS_AS to
SQL_Latin1_General_CP1_CI_AS. Or can that only be changed on the
initial installation.
ThanksHi
"jsully79" wrote:
> Hello
> We just upgraded to SQL server 2000 sp3a from sp4 . Could the
> collation
> change upgrading to SP4 (from SQL_Latin1_General_CP1_CS_AS to
> SQL_Latin1_General_CP1_CI_AS. Or can that only be changed on the
> initial installation.
> Thanks
I have not know the collation change when applying the service pack. To do
for the whole system would have required alot of work. Are you asking about a
specific database/column/setting?
John

Changing Collation SQL 2000 sp4

Hello
We just upgraded to SQL server 2000 sp3a from sp4 . Could the
collation
change upgrading to SP4 (from SQL_Latin1_General_CP1_CS_AS to
SQL_Latin1_General_CP1_CI_AS. Or can that only be changed on the
initial installation.
Thanks
Hi
"jsully79" wrote:

> Hello
> We just upgraded to SQL server 2000 sp3a from sp4 . Could the
> collation
> change upgrading to SP4 (from SQL_Latin1_General_CP1_CS_AS to
> SQL_Latin1_General_CP1_CI_AS. Or can that only be changed on the
> initial installation.
> Thanks
I have not know the collation change when applying the service pack. To do
for the whole system would have required alot of work. Are you asking about a
specific database/column/setting?
John

Changes to MDX when upgrading to AS 2005

I was wondering if anybody has upgraded to AS 2005 without having to change
their stored MDX extensively to work with the new AS model. The company I
work for has 100's of reports with 100's of MDX queries that might
potentially need to be updated because 1) it's just not possible to do things
the old way and 2) we want to utilize the new features of SSAS 2005.
I'm looking for general advice or maybe some good things to know when
upgrading to SSAS 2005 while using SSRS 2005 as your enterprise reporting
framework.
Thanks,
Joshhavent got anything definitive, other than to say our mdx based reports
ported fine. Only issue was 2005 was stricter with having links in page
footers which caused a warning in 2000 but wouldnt work in 2005
"JSI" <JSI@.discussions.microsoft.com> wrote in message
news:B909920B-124C-4B09-B584-2EF40847F3B6@.microsoft.com...
>I was wondering if anybody has upgraded to AS 2005 without having to change
> their stored MDX extensively to work with the new AS model. The company I
> work for has 100's of reports with 100's of MDX queries that might
> potentially need to be updated because 1) it's just not possible to do
> things
> the old way and 2) we want to utilize the new features of SSAS 2005.
> I'm looking for general advice or maybe some good things to know when
> upgrading to SSAS 2005 while using SSRS 2005 as your enterprise reporting
> framework.
> Thanks,
> Josh