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.

SQL SERVER - Compatibility Level 80 and Table Hint Behavior compatibilitylevel1-800x182

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

ProductCompatibility Level
SQL Server 200080
SQL Server 200590
SQL Server 2008/R2100
SQL Server 2012110
SQL Server 2014120
SQL Server 2016130
SQL Server 2017140

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.

Solarwinds

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)

Solarwinds
, ,
Previous Post
SQL SERVER – When to Turn On Optimize for Ad Hoc Workloads?
Next Post
SQL SERVER – Performance Observation of TRIM Function

Related Posts

5 Comments. Leave new

  • Hi ,
    Thanks.I do use both “nolock” and with (nolock).Here after i keep in mind.

    Reply
  • wilfred van dijk
    October 24, 2017 12:49 pm

    Have a look at master.sys.dm_os_performance_counters, query for “where object_name like ‘%deprecated%’ “. There’s a row where instance_name = ‘Table hint without WITH’ (and a lot of other issues)

    Reply
  • wow, quite amazing.

    Reply
  • Hi Pinal,

    Thanks for your beautiful blog,

    How can I get to know my Server Box Name through my SQL instance I am connected with. Is their any query to retrieve server name from server instance you are connected with?
    For example:- From my machine I am accessing ABCSQLInstance through SSMS. what is the way to find Server Name of this ABCSQLInstance ?.

    Prob. If i dont know my Server Name I can not go to RDP server to run HostName command on command prompt

    Thanks
    B Raj

    Reply
  • Robert Djabarov
    October 25, 2017 12:11 am

    NOLOCK without parentheses even in SQL 2000 was viewed as an alias. Maybe in 6.5 or 7.0?

    Reply

Leave a Reply

Menu