SQL SERVER – Applying NOLOCK to Every Single Table in Select Statement – SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SQL SERVER - Applying NOLOCK to Every Single Table in Select Statement - SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED nolock The other day, during SQL Server Performance Tuning Practical Workshop, I walked into a very interesting situation. While tuning a query we had a situation when we had to check if the query is locked during the execution of not. To test our theory out we had to put a nolock hint for every single table of the long query. However, it was no way possible to do so as the Stored Procedure was super huge and involved over 90 tables and 14 views in multiple SQL Statements. It was indeed a huge challenge for a developer team to modify that SP. If you ever face such situations, you should not stress out. There is a much simpler way to read uncommitted data.

Before you continue reading this blog post, please note that I personally do not prefer to use to NOLOCK hints in my business, as it will read the dirty data and often reading uncommitted data creates problems with database integrity. There are many different ways to tune your query rather than using NOLOCK hint or using read uncommitted transaction isolation.

Let us see first a simple example how NOLOCK hint works with multiple tables.

SELECT *
FROM [Application].[Cities] ct WITH (NOLOCK)
INNER JOIN [Application].[StateProvinces] sp WITH (NOLOCK)
ON ct.StateProvinceID = sp.StateProvinceID
GO

Now we will convert the same script to use read uncommitted transaction isolation.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
SELECT *
FROM [Application].[Cities] ct
INNER JOIN [Application].[StateProvinces] sp
ON ct.StateProvinceID = sp.StateProvinceID
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO

Technically, there is absolutely no difference between the performance and impact of both the methods. I prefer to use the second method more often as it is easier to write and test. Again, if possible, I like to stay away from reading uncommitted methods to read the data.

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

, , ,
Previous Post
SQL SERVER- High CPU and Transaction Type WorkFileGroup_fake_worktable and Workfile
Next Post
Practical Real World Performance Tuning – Reviews and Feedback

Related Posts

11 Comments. Leave new

  • try with (nolock) with using cross apply or outer apply ??

    Reply
  • James DiBernardo
    September 4, 2018 3:01 am

    What permissions does the user running the query need for :
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    Reply
    • I don’t think there is a special permission needed. Are you getting any error when you run

      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

      Please help me to reproduce the error.

      Reply
  • If both are same, then would prefer to use (NoLock) hint. Have flexibility to apply for the tables only which are fine to read dirty data.

    Reply
  • What if SP don’t reach till last statement due to some error, will it affect other running SPs?

    Reply
  • For SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED the control is with user. If user forgets and did a select & his session is active, we are not able to do truncate and reload as part of ETL process. Is there any way this can be set at DB or schema level by DBA?

    Reply
  • Timothy james Ultican
    November 8, 2018 3:09 am

    Pinal, I’ve been following your website, I think since the late 90’s, maybe early 2000’s love the blog, keep up the great work!!

    Question, shouldn’t there be a preface with Transaction Isolation Level read uncommitted? For example, if I have an EDW that has a nightly ETL batch starting at midnight, and as long as I don’t have end user read/running reports until after the nightly batch is finished then theoretically I don’t ever have to worry about a dirty read?? Am I understanding the Isolation level correctly? Thanks!!!

    Reply

Leave a Reply

Menu