Is there a negative impact on using SQL Server with lower compatibility

We have SQL Server 2008 R2, which we use for a database with a compatibility level set to 2005 (90).

We, having updated it to 2008, at some point, but nevertheless I would like to know if the database will have a lower level of compatibility, which will negatively affect its performance?

I found this question , which implies that there may be a negative effect, but for an older version of SQL Server.

+4
source share
1 answer

I am currently updating SQL Server, and we are working to increase compatibility to 100. I have not seen performance differences in our applications. The reason we are only partially making changes is to take advantage of some of the new SQL Server features. We are at level 80, so the main reason is that we cannot upgrade to 2008 R2 until we work at a higher level. Be careful that you keep up with your current compatibility level until you can update your database.

We went through our code base to remove manually inserted tooltips that used outdated syntax. It turned out that removing tooltips actually improved performance. Presumably, this is due to the fact that query optimizers have improved over the past decade to such an extent that they can be better optimized than the optimization chosen by the developers ten years ago. So indirectly, we found that improving compatibility improved performance, but only because we had to change our application code base.

As soon as we increase the compatibility level, we will be able to use new functions that will improve performance, but we do not see that working with a database at a lower compatibility level has lower performance than working with the same database (and applications) for more high level of compatibility.

+1
source

Source: https://habr.com/ru/post/1437754/


All Articles