Are there any performance benefits of using SQL Server 2008 over SQL Server 2005?

Question

Hi,

Are there any performance benefits of using SQL Server 2008 over SQL Server 2005?

Answer 1

Moving a single database from SQL Server 2005-2008 will not notice a difference really. However, there are new tools and options available in SQL Server 2008 that you MIGHT be able to leverage to provider better performance later on in your application.

One item that comes to mind is filtered indexes. Allowing to create an index on a subset of information.

Answer 2

There may be new features in the engine which execute queries in different ways. This includes changes to the optimiser.

Therefore, the only way you can POSSIBLY tell, is to gather detailed performance data from your application on MSSQL2005, and then repeat the experiment on the same (production-quality) hardware with SQL2008.

You will need to make sure your application works correctly- such a migration can't be done lightly as any change could introduce bugs.

Also, the new version of the database could have performance regressions - which you need to be very careful about.

So in summary:

• Benchmark YOUR application on SQL2005
• Benchmark it on SQL2008
• Use the same production-grade test hardware in your lab both times
• Don't run VMs (unless that's what you do in production)
• Don't change other parameters

This may not be easy if your application is big / complicated.

Answer 3

Yes. You can compress data in SQL 2008 which can have drastic impact on backup and data transfer times.

Answer 4

Actually SQL2008 has built-in compression that you can enable out of the box which could definately improve performance, but it may depend on what is being returned. I would try this option and benchmark to see if you feel its a worthy change.