SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


WITH(NOLOCK) vs WITH(READPAST)


WITH(NOLOCK) vs WITH(READPAST)

Author
Message
kevin_nikolai
kevin_nikolai
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3717 Visits: 558
SELECT tblRates.Interest_Rate, tblRates_Types.Rate_Name,
tblRates_Types.Description, tblRates.Effective_Date
FROM tblRates with (nolock)
INNER JOIN tblRates_Types with (nolock)
ON tblRates.Rate_Type_PK = tblRates_Types.Rate_Type_PK

SELECT tblRates.Interest_Rate, tblRates_Types.Rate_Name,
tblRates_Types.Description, tblRates.Effective_Date
FROM tblRates with (readpast)
INNER JOIN tblRates_Types with (readpast)
ON tblRates.Rate_Type_PK = tblRates_Types.Rate_Type_PK

==================

Select COUNT(*)
From tblEntity_Add_Info_Data WITH(NOLOCK)

Select COUNT(*)
From tblEntity_Add_Info_Data WITH(READPAST)

(Count = 41159)

==================

SELECT *
FROM tblContract_Instal1 WITH(NOLOCK)

SELECT *
FROM tblContract_Instal1 WITH(READPAST)

(368887 rows)

***
Both WITH(NOLOCK) and WITH(READPAST) queries have identical performance relating to index usage
as displayed by estimated execution plan.

Question:
If using INNER JOINS, what do you suggest to improve T-SQL query performance (besides adding indexes). Should I use nolock, readpast, etc.

==================================
More info:

It is recommended you use NOLOCK in joins.
You can only use NOLOCK in SELECT statements. This includes inner queries, and the SELECT clause of the INSERT statement. Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement.

The advantage of NOLOCK is that it does not have to wait for the other transaction to complete, but the disadvantage is that it could give you incorrect data.
If the UPDATE statement later gets committed, then you get lucky and have the correct data, but if the UPDATE gets rolled back the result set is incorrect.

SQL Server mostly uses unordered clustered index scans when NOLOCK is specified,
this can cause duplicate records to be returned when page splits occur while your query is scanning the index.

Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous
access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking).

Regards
Kevin
sonyt65@yahoo.com
SQLZ
SQLZ
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10846 Visits: 940
Hi Kevin,

One thing you should realise is that neither NOLOCK or READPAST do anything to improve query performance at all. All they do is control concurrency, which can have the benefit of seemingly make things look quicker when many processes are trying to access the same data.

I would probably never use READPAST as this can give you different results every time. READPAST ignores locked data and doesn't return it. Whereas NOLOCK still returns that data, albeit giving you potentially dirty data.

If you select from a table that would typicall return 100 rows, when using READPAST, if 10 of those rows are being updated, then you'd only get back 90 rows. With NOLOCK you'd get back all 100 rows.

So as long as you're aware of the drawbacks of using NOLOCK (and you can live with it) and if you're dealing with concurrency problems then NOLOCK can be of benefit.

Karl
source control for SQL Server
GilaMonster
GilaMonster
SSC Guru
SSC Guru (888K reputation)SSC Guru (888K reputation)SSC Guru (888K reputation)SSC Guru (888K reputation)SSC Guru (888K reputation)SSC Guru (888K reputation)SSC Guru (888K reputation)SSC Guru (888K reputation)

Group: General Forum Members
Points: 888124 Visits: 48655
SQL Server mostly uses unordered clustered index scans when NOLOCK is specified,
this can cause duplicate records to be returned when page splits occur while your query is scanning the index.

Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system.


With or without nolock, SQL will use the most appropriate index for the query. It won't use the cluster just because a nolock is specified.
Nolock is not considered a general good practice. It's overused and is often used to hide the fact that there are other issues on the server. If you have blocking issues, the best thing to do about them is to fix the root cause, not hide the symptoms

Better option, than using locking hints, would be to make sure that your indexes are appropriate to the queries, that the queries are written as optimally as possible and that your transactions are as short as possible. Those three will ensure that locks are taken at the lowest level of granularity possible and held for the shortest possible time.

If you've done all that and still have blocking problems, consider using snapshot or read-committed snapshot isolation. Just be aware of the impact on TempDB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search