SQL Server 2000 vs SQL Server 2008 Query Performance

Question

I'm working with a client who had a SQL Server 2008 converted from a SQL Server 2000 DB and one of the queries has quite dramatically increased in time since it was on SQL Server 2000.

However, if I change the compatibility level to 2008 in the DB, the query goes like a rocket (40-50 times faster).

The query does use a number of UDFs.

My questions: - are there issues with running SQL2000 compatibility in SQL Server 2008 - has SQL Server 2008 improved the performance when using UDFs?

Want us to roll the 8 ball and guess? Post code, schema, perhaps actual plans. — Remus Rusanu
– Remus Rusanu, Commented Nov 20, 2009 at 21:34

Aaron Bertrand · Accepted Answer · 2009-11-20 21:35:03Z

5

There are some other things you might want to do after upgrading. See the "After upgrading..." section here: http://msdn.microsoft.com/en-us/library/bb933942.aspx

answered Nov 20, 2009 at 21:35

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HLGEM Over a year ago

Excellent link. It is very important to update statistics for instance.

Collectives™ on Stack Overflow

SQL Server 2000 vs SQL Server 2008 Query Performance

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related