4

We have SQL Server 2008 R2 which we are using for a database with a compatibility level set as 2005 (90).

We will by upgrading it to 2008 at some point, but in the meantime I would like to know if having the database in a lower compatibility level is this going to have a negative effect on its performance?

I found this question that implies there could be a negative effect, but it's for an older version of SQL Server.

Community
  • 1
  • 1
Iain Hoult
  • 3,889
  • 5
  • 25
  • 39
  • The answer you are referencing seems to confuse running in 2000 compat mode and running on an actual SQL 2000. – usr Oct 04 '12 at 09:57
  • when i update my db compat 90 to 100, i got error in some store procedure, there was one commont statement which was not acceptable in higher version, it was something like set @value=select.. or select @value=, now i forget but just want to aware you – Chhatrapati Sharma Oct 04 '12 at 13:58

1 Answers1

1

I am going through a SQL Server upgrade now and we are working to increase the compat level to 100. I have not observed performance differences in our applications. The reason that we are making the changes only in part to take advantage of some new features of SQL Server. We are at level 80, so the main reason is that we cannot upgrade to 2008 R2 until we are running at a higher level. Be careful that you don't fall behind the current compat level so far that you can't upgrade your database.

We have been going through our code base to remove manually inserted hints that used a deprecated syntax. It turned out that removing the hints actually improved performance. Presumably, this is because the query optimizers have improved over the last decade to the point that it can better optimize than the optimizations chosen by developers a decade ago. So indirectly, we found that increasing the compat level has improved performance, but only because we had to change our application code base.

Once we increase the compat level, we may be able to take advantage of new features that will improve performance, but we have not see that running a database at a lower compat level has lower performance than running the same database (and applications) at a higher compatibility level.

Bob Folkerts
  • 376
  • 2
  • 11