November 3, 2008 at 1:59 pm
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
November 4, 2008 at 5:05 am
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.
November 4, 2008 at 6:54 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply