Upgrade from 2000 to 2008

Question

Upgrade from 2000 to 2008

djones-1062714

SSC Enthusiast

Points: 160
More actions
April 7, 2009 at 8:00 am

#202653

We are considering an upgrade from 2000 to 2008. My question is, if we set the compatibility level to 80 so that all our existing T-SQL still works will we see any performance improvement? Basically I am trying to figure out if it even makes sense to upgrade if we are just going to set the compatibility level to 80. If we will see improvements then I would not have an issue with making the move and then slowly converting the T-SQL over time and we could set the level to 100 later.
Thanks,
David

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 1

djones (4/7/2009)
We are considering an upgrade from 2000 to 2008. My question is, if we set the compatibility level to 80 so that all our existing T-SQL still works will we see any performance improvement? Basically I am trying to figure out if it even makes sense to upgrade if we are just going to set the compatibility level to 80. If we will see improvements then I would not have an issue with making the move and then slowly converting the T-SQL over time and we could set the level to 100 later.
Thanks,
David

Hi David,

Firstly, the database compatibility level is a database specific parameter and only affects the database you apply a value for, thus not a server-wide configuration property.

In terms of seeing a performance improvement, changing the compatibility level will not provide a performance improvement. The improvement in performance is more down to existing code being designed optimally and correct indexes being applied\rebuilt. As you may or may not know, there are differences between a SQL Server 2000 engine and a SQL Server 2008 engine, thus any real performance gains will be a the engine level, assuming regular maintenance task have been completed as normal.

Unless you are using particular keywords in your T-SQL, using Full-Text feature, DBCC CHECKDB with NOINDEX, then you should be find in terms of running SQL Server 2008 in native compatibility mode. Please review SQL Server 2008 Books-Online, as there is loads of information on this particular question.

Thanks,

Phillip Cox

djones-1062714 SSC Enthusiast Points: 160 More actions · Answer 2

Hi David,
Firstly, the database compatibility level is a database specific parameter and only affects the database you apply a value for, thus not a server-wide configuration property.
In terms of seeing a performance improvement, changing the compatibility level will not provide a performance improvement. The improvement in performance is more down to existing code being designed optimally and correct indexes being applied\rebuilt. As you may or may not know, there are differences between a SQL Server 2000 engine and a SQL Server 2008 engine, thus any real performance gains will be a the engine level, assuming regular maintenance task have been completed as normal.
Unless you are using particular keywords in your T-SQL, using Full-Text feature, DBCC CHECKDB with NOINDEX, then you should be find in terms of running SQL Server 2008 in native compatibility mode. Please review SQL Server 2008 Books-Online, as there is loads of information on this particular question.
Thanks,
Phillip Cox

Everything you said I beleive I understand. I know that the compatability level doesn't offer any performance increase in itself. In order to run my database exactly as is in 2000, under 2008, I will need to set it to 80. For example, there are many places where we have written SQL joins using *'s versus the INNER/OUTER LEFT/RIGHT JOIN syntax. It is my understanding that in order to run that under the default compatability level you must convert the SQL to use the proper JOIN syntax. So what my specific question is... If I set the compatability to 80, so that I can migrate my database with out changing anything, will I see some performance increase over 2000 with the improved engine in 2008.

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 3

Hi David,

Thanks for clarification!

You should see a performance improvement, as the database and query engines have been further improved upon from SQL Server 2005.

As with any performance related questions, I would strongly suggest you test, test and test until you are satisfied with results.

Thanks,

Phillip Cox

djones-1062714 SSC Enthusiast Points: 160 More actions · Answer 4

Phillip Cox (4/7/2009)
Hi David,
As with any performance related questions, I would strongly suggest you test, test and test until you are satisfied with results.
Thanks,
Phillip Cox

I agree 100%

f.eveilleau SSC Enthusiast Points: 118 More actions · Answer 5

Hi David,

We are currently in same situation than when you post your message about performance and compatibility level.

Did you do it ? Did you observe performance improvement ?

Thanks,

Fred

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 6

My suggestion would be to capture profiler trace on SQL Server 2000 and replay trace on SQL Server 2008. You can analyze reports very easily using RML utility.

I have done such tests whenever perform any upgrade and you may refer an article about how to use RML utility to replay trace.

[font="Verdana"]--www.sqlvillage.com[/size][/font]

f.eveilleau SSC Enthusiast Points: 118 More actions · Answer 7

f.eveilleau

SSC Enthusiast

Points: 118

August 23, 2010 at 12:35 am

#1210252

Great idea, i will follow it !

Thanks a lot.

Fred

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 8

You are Welcome!!!

[font="Verdana"]--www.sqlvillage.com[/size][/font]