SQL SERVER – Compatibility Level 80 and Table Hint Behavior

It has been a while since I worked with SQL Server 2000. However, as an independent consultant, SQL Server never stops to surprise us. Recently, during one Comprehensive Database Performance Health Check, I found a very interesting situation which is about compatibility level 80. If you are not familiar with compatibility level, it sets database behaviors to be compatible with the specified version of SQL Server.

Here is a quick reference for various versions of SQL Server with their compatibility level.

Every compatibility level has some features which behaves differently with different version of SQL Server.

Quick Story – Performance Issue

For example, my customers were suddenly getting lots of deadlocks and they faced quite a few performance issues after they upgrade from SQL Server 2000 to SQL Server 20016. They had nightmares and their results were inconsistent and performance was unpredictable. To be super honest, I put lots of efforts, but again, the performance improvement was very little as they were facing some strange issue which none of us were able to understand.

Most of the time, I am able to help my customer without going into the nitty gritty of the coding. I am just able to help them with my standard performance tuning techniques. However, this was a special case. After a while, I requested them to show me the code of the SP which is performing very poorly.

Once I started to look at the code, I suddenly figured out what is going wrong with the code.

Discovery – Table Hints

I quickly discovered that my customers were using old style hints which were valid in SQL Server 2000. Here is the example of the code.

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

This was perfectly fine in SQL Server 2000. The hint nolock was applied to the table just fine and the table was doing a dirty read by applying nolock on it. When you try to run the same code in SQL Server 2016, it also runs fine and there are no errors. However, in SQL Server 2016, the hint NOLOCK becomes an actually an alias for the table [Human Resources]. [Employee].

Let me repeat, the query hint syntax which was valid in SQL Server 2000, becomes a valid alias for the table in later versions of the SQL Server and it does not give any error. The matter of fact, there are no locks applied to the original table.

Now, I am not a big fan of locks and particular I hate to write NOLOCKS for my tables. However, I was in a situation where I can give my customer any philosophical knowledge, they just wanted their system back into action as soon as possible as the end clients were struggling to do anything on the system.

Resolution

I suggested them a new syntax which is valid for SQL Server 2005 and the later version for implementing table locks. Here is the script.

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

Well, that’s it. My customer immediately started to modify all the places where they had used query hints and it took around one hour for them to go through entire codebase and make necessary changes. After all the changes were completed, when we checked our system’s performance, we realized it is running much faster due to better hardware and all the other optimization logic which we had deployed early in the day.

Trust me, SQL Server never stops to teach me new things!

Reference: Pinal Dave (https://blog.sqlauthority.com)