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