Unanswered: Query execution time different in SQL Server 2000 than 2005
I have migrated a database from 2000 to 2005, and have a query which simply does an inner join of 2 tables than order it by name in asc order, it takes around 10 seconds to run in 2000 but around 60 second to run in 2005, what could be causing this?
I have reorganize and rebuild the INDEX and update the STATISTIC on 2005, ran a query to see the % of fragmentation on index, both index average fragmentation is nearly 0% (this is expected as I rebuild the index), so it isn't an indexing problem.
I look at the execution plans of the query on 2000 and 2005, the only difference is on 2005 a table spool/lazy spool is occurring which isn't occurring on 2000, could this be the cause???