As a SQL Server consultant, I am constantly amazed by the various features and functionalities that SQL Server offers. Recently, during a Comprehensive Database Performance Health Check, I encountered an interesting situation related to compatibility level. If you’re not familiar with compatibility level, it determines the behavior of a database to be compatible with a specific version of SQL Server.

Let’s take a quick look at the compatibility levels for different versions of SQL Server:

Each compatibility level has its own set of features that behave differently with different versions of SQL Server.

Performance Issue: A Case Study

Let me share an interesting case study that highlights the impact of compatibility level on performance. One of my clients recently upgraded their SQL Server from version 2000 to 2016. However, after the upgrade, they started experiencing frequent deadlocks and performance issues. The system’s performance became inconsistent and unpredictable.

As a consultant, I usually rely on standard performance tuning techniques to help my clients. However, this particular case required a deeper investigation. Upon analyzing the code, I discovered that the client was using old-style table hints that were valid in SQL Server 2000. Here’s an example of the code:

SELECT * FROM [HumanResources].[Employee] NOLOCK;

In SQL Server 2000, this code would work perfectly fine, applying the NOLOCK hint to the table and performing a dirty read. Surprisingly, when the same code was executed in SQL Server 2016, it ran without any errors. However, in SQL Server 2016, the NOLOCK hint became an alias for the table [HumanResources].[Employee]. This meant that no locks were applied to the original table.

While I personally prefer not to use table hints like NOLOCK, I understood that my client needed a quick solution to get their system back on track. I suggested a new syntax that is valid for SQL Server 2005 and later versions:

SELECT * FROM [HumanResources].[Employee] WITH(NOLOCK);

My client immediately started modifying all the places where they had used query hints, and it took them about an hour to make the necessary changes throughout their codebase. Once all the changes were completed, we noticed a significant improvement in the system’s performance. This was due to the combination of better hardware and the optimization logic we had implemented earlier.

SQL Server never ceases to teach me new things, and this experience highlighted the importance of understanding compatibility levels and their impact on performance. It’s crucial to stay updated with the latest syntax and best practices to ensure optimal performance and compatibility with newer versions of SQL Server.

Thank you for reading!