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)
12 Comments. Leave new
try with (nolock) with using cross apply or outer apply ??
What permissions does the user running the query need for :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
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.
What if SP don’t reach till last statement due to some error, will it affect other running SPs?
It doesn’t affect other SPs as its scope is for that session and command.
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?
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!!!
I added SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED on the top of the stored procedure eg.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
Create PROCEDURE [dbo].xxxx
as
begin
zzzzz
end
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
I did on Friday but on Monday from all stored procedure Dirty Read line were missing whereas other changes were still as it is.