June 27, 2015 at 10:32 pm
Jeff Moden (6/26/2015)
ScottPletcher (6/26/2015)
Sean Lange (6/26/2015)
ScottPletcher (6/25/2015)
GilaMonster (6/25/2015)
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Why does everyone keep repeating that?
Scott you have been around sql long enough to know the point Gail was making here. It is often used as a magic "go faster" button by people who do not even pretend to understand the nuances of what it does. I am one of those people who shudder when I see that hint and make frequent comments about not using it. I typically tell people it is not a mechanism just to make queries run faster. It has some very huge issues that very few people understand. As with everything it has its place, but that place should not be on every single query. I can't begin to count the number of times people's response to why they use it is "because the technical lead says to use it on every query to make them faster".
All very true. But one shouldn't say "NOLOCK does not make queries faster", as people repeatedly do here, because that's just, sadly, factually false. We must instead stress the invalid, and missed, data that NOLOCK can show.
Until you can actually provide code that proves otherwise, I'll also have say that "NOLOCK does not make queries faster". The only thing that I've seen that "makes them faster" is when blocking occurs. Because of dirty reads, it doesn't wait on most blocking. By itself, though, I've not seen any proof from you or anyone else that WITH(NOLOCK) causes queries to run faster when blocking is not present.
Again... not ganging up on you here. It's an interesting claim that I'd like to see be true but I'm not taking anyone's word, best guess, or text only article (even if it's from MS) on the subject. Show us some code that proves it, please.
I can't do that right now. But to me it's completely illogical to say that taking locks is zero overhead. If NOLOCK doesn't improve performance at all, then it simply shouldn't -- and I say wouldn't -- exist, given its inherent vast disadvantages.
As I stated above, even if you can't directly see a difference on a simple query, there must be some overhead. Particularly so where it leads to deadlocks because the SELECT was doing a scan of an index.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 30, 2015 at 7:41 am
ScottPletcher (6/25/2015)
sgmunson (6/25/2015)
ScottPletcher (6/25/2015)
GilaMonster (6/25/2015)
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Why does everyone keep repeating that? Of course NOLOCK hints make queries run faster, even if you can't easily or directly observe the time difference. Because taking locks requires some CPU time and resources, not taking such locks saves time.
That is not to say that NOLOCK is a good idea overall, and NOLOCK certainly shouldn't be used routinely/rotely, especially if you don't understand the consequences of it. But it does save resources and it does have appropriate uses under the right conditions.
They repeat it because without taking those locks, there's no guarantee that you are operating on current information, so data integrity is at risk. The only time that you should ever use that hint is when being slightly out of date isn't an issue, and you're not going to represent that data as authoritative or use it as a source for an UPDATE.
That's not what they're claiming -- they claiming definitively that it does not make it faster ("[NOLOCK hints] don't make queries faster"). I dispute that again. It is indeed less overhead to do reads that way.
Understood but, despite your claim, the demo code that I provided would seem to be proof that the use of NOLOCK does not produce any increase in performance at all whether it sounds logical or not. In order to anchor your argument, you would need to produce some code that supports your argument.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2015 at 10:11 am
This is all rather interesting, so I thought I'd chime in.
Based on what I've tested, Scott is technically correct. Under the right conditions, even where it is not preventing blocking, NOLOCK can be faster than a query without the hint.
Now, that's very hard to measure for two reasons.
First, the overhead of locking is very, very small. On the lab machine I most recently tested, it usually works out to around .4 microseconds per lock. So, unless you're taking out millions of locks, it's very easy for the locking overhead to be washed out by normal run-to-run variance in results.
If I run Jeff's sample (with a regular table, not a temp table, to avoid that confounding factor) I only end up with about 10000 pages. Even if running the query took out all 10000 page locks (which it doesn't, and that will bring us to the second reason it's hard to measure this), that's about 4 milliseconds, which is easily missed because of run-to-run variance.
Second, because of lock escalation, you're not even going to see that 4 ms difference. After the first 5000 pages are locked, the process will just escalate to a table lock if it can, so now we're down to about 2 ms difference. That's going to be easily wiped out by normal variance in duration.
If you disable lock escalation, and then use a ROWLOCK hint, you can start to measure the difference if you have enough rows. I've updated Jeff's sample to illustrate all this.
--DROP TABLE perftest;
GO
--===== Create and populate a heap on the fly
SELECT TOP 1000000 ac1.*
INTO perftest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
--===== Prime the pump just to make everything even for both tests
DECLARE @Bitbucket SYSNAME;
SELECT @Bitbucket = [name]
FROM perftest;
GO
--===== REPEATABLE READ will let us run the select within a transaction,
--===== and since the locks will be held until we rollback, we can see
--===== what locks were taken out
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
--===== This is the WITH (NOLOCK) code
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest WITH (NOLOCK);
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
--===== This is the un-hinted code
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
--Now disable lock escalation and repeat the above
ALTER TABLE perftest SET (LOCK_ESCALATION=DISABLE)
--===== This is the WITH (NOLOCK) code
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest WITH (NOLOCK);
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
--===== This is the un-hinted code
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
--===== Now let's force row level locks. We should see a noticeable difference in runtime now.
--===== At .4 microseconds, a million locks should add about 400 milliseconds to the duration
--===== of the query.
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest WITH (ROWLOCK)
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
So, yes, there is some overhead, but under any normal circumstances it won't even be measurable. You actually have to force SQL Server to behave in the worst way possible (lock escalation disabled and force row locks) to reliably measure it.
In practice, then, any speed increases will be solely due to avoiding blocking, and with that comes with all the usual problems of NOLOCK. To be fair, we already knew that, though 🙂
Cheers!
June 30, 2015 at 1:24 pm
Jacob Wilkins (6/30/2015)
This is all rather interesting, so I thought I'd chime in.Based on what I've tested, Scott is technically correct. Under the right conditions, even where it is not preventing blocking, NOLOCK can be faster than a query without the hint.
Now, that's very hard to measure for two reasons.
First, the overhead of locking is very, very small. On the lab machine I most recently tested, it usually works out to around .4 microseconds per lock. So, unless you're taking out millions of locks, it's very easy for the locking overhead to be washed out by normal run-to-run variance in results.
If I run Jeff's sample (with a regular table, not a temp table, to avoid that confounding factor) I only end up with about 10000 pages. Even if running the query took out all 10000 page locks (which it doesn't, and that will bring us to the second reason it's hard to measure this), that's about 4 milliseconds, which is easily missed because of run-to-run variance.
Second, because of lock escalation, you're not even going to see that 4 ms difference. After the first 5000 pages are locked, the process will just escalate to a table lock if it can, so now we're down to about 2 ms difference. That's going to be easily wiped out by normal variance in duration.
If you disable lock escalation, and then use a ROWLOCK hint, you can start to measure the difference if you have enough rows. I've updated Jeff's sample to illustrate all this.
--DROP TABLE perftest;GO
--===== Create and populate a heap on the fly
SELECT TOP 1000000 ac1.*
INTO perftest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
--===== Prime the pump just to make everything even for both tests
DECLARE @Bitbucket SYSNAME;
SELECT @Bitbucket = [name]
FROM perftest;
GO
--===== REPEATABLE READ will let us run the select within a transaction,
--===== and since the locks will be held until we rollback, we can see
--===== what locks were taken out
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
--===== This is the WITH (NOLOCK) code
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest WITH (NOLOCK);
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
--===== This is the un-hinted code
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
--Now disable lock escalation and repeat the above
ALTER TABLE perftest SET (LOCK_ESCALATION=DISABLE)
--===== This is the WITH (NOLOCK) code
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest WITH (NOLOCK);
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
--===== This is the un-hinted code
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
--===== Now let's force row level locks. We should see a noticeable difference in runtime now.
--===== At .4 microseconds, a million locks should add about 400 milliseconds to the duration
--===== of the query.
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest WITH (ROWLOCK)
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
So, yes, there is some overhead, but under any normal circumstances it won't even be measurable. You actually have to force SQL Server to behave in the worst way possible (lock escalation disabled and force row locks) to reliably measure it.
In practice, then, any speed increases will be solely due to avoiding blocking, and with that comes with all the usual problems of NOLOCK. To be fair, we already knew that, though 🙂
Cheers!
So let me summarize in my own words...
Using NOLOCK can actually boost performance but on any tables less than hundreds of millions of rows the performance is so small it is nearly immeasurable. On the other hand, there are dozens and dozens of articles that go into great detail about the pitfalls and downsides of using NOLOCK with as few as a thousand rows.
Seems to me that sort of suggests that hint has no real usage in almost every situation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2015 at 1:55 pm
I reiterate my thought on using the NOLOCK/READUNCOMMITTED hints, those are as clever as ignoring traffic lights, they will get you there quicker but if and when an error occurs, the consequences can be grave. I find the use of these hints coincide with a bad quality code bases, wrong schema normalization etc. where locking and blocking is a problem originating from those causes. The performance "benefits" are negligible in comparison to the potential damages. And by the way, NOLOCK should be IGNORELOCK as still some locks are there but other locks are ignored. In other words, one cannot keep the cake and eat it, if working with an ACID compliant RDMS, locks are just like the Taxes, as unavoidable as death, everyone has to pay.
😎
June 30, 2015 at 2:08 pm
If the performance improvement is almost immesurable, why do we have NOLOCK/READ UNCOMMITED?
Basically, they're useful when we want an approximate result of a changing data set. These cases happen and we should be thankful to have this option available to prevent some problems that might come from blocking. That said, I won't ever recommend (as some people do) to use it on every single table on every single query.
June 30, 2015 at 7:11 pm
Sean Lange (6/30/2015)
Jacob Wilkins (6/30/2015)
This is all rather interesting, so I thought I'd chime in.Based on what I've tested, Scott is technically correct. Under the right conditions, even where it is not preventing blocking, NOLOCK can be faster than a query without the hint.
Now, that's very hard to measure for two reasons.
First, the overhead of locking is very, very small. On the lab machine I most recently tested, it usually works out to around .4 microseconds per lock. So, unless you're taking out millions of locks, it's very easy for the locking overhead to be washed out by normal run-to-run variance in results.
If I run Jeff's sample (with a regular table, not a temp table, to avoid that confounding factor) I only end up with about 10000 pages. Even if running the query took out all 10000 page locks (which it doesn't, and that will bring us to the second reason it's hard to measure this), that's about 4 milliseconds, which is easily missed because of run-to-run variance.
Second, because of lock escalation, you're not even going to see that 4 ms difference. After the first 5000 pages are locked, the process will just escalate to a table lock if it can, so now we're down to about 2 ms difference. That's going to be easily wiped out by normal variance in duration.
If you disable lock escalation, and then use a ROWLOCK hint, you can start to measure the difference if you have enough rows. I've updated Jeff's sample to illustrate all this.
--DROP TABLE perftest;GO
--===== Create and populate a heap on the fly
SELECT TOP 1000000 ac1.*
INTO perftest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
--===== Prime the pump just to make everything even for both tests
DECLARE @Bitbucket SYSNAME;
SELECT @Bitbucket = [name]
FROM perftest;
GO
--===== REPEATABLE READ will let us run the select within a transaction,
--===== and since the locks will be held until we rollback, we can see
--===== what locks were taken out
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
--===== This is the WITH (NOLOCK) code
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest WITH (NOLOCK);
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
--===== This is the un-hinted code
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
--Now disable lock escalation and repeat the above
ALTER TABLE perftest SET (LOCK_ESCALATION=DISABLE)
--===== This is the WITH (NOLOCK) code
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest WITH (NOLOCK);
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
--===== This is the un-hinted code
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
--===== Now let's force row level locks. We should see a noticeable difference in runtime now.
--===== At .4 microseconds, a million locks should add about 400 milliseconds to the duration
--===== of the query.
BEGIN TRANSACTION
DECLARE @Bitbucket SYSNAME;
SET STATISTICS TIME,IO ON;
SELECT @Bitbucket = [name]
FROM perftest WITH (ROWLOCK)
SET STATISTICS TIME,IO OFF;
SELECT COUNT(*), request_type, request_mode, resource_type
FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY request_type,request_mode,request_owner_type, resource_type
ROLLBACK
So, yes, there is some overhead, but under any normal circumstances it won't even be measurable. You actually have to force SQL Server to behave in the worst way possible (lock escalation disabled and force row locks) to reliably measure it.
In practice, then, any speed increases will be solely due to avoiding blocking, and with that comes with all the usual problems of NOLOCK. To be fair, we already knew that, though 🙂
Cheers!
So let me summarize in my own words...
Using NOLOCK can actually boost performance but on any tables less than hundreds of millions of rows the performance is so small it is nearly immeasurable. On the other hand, there are dozens and dozens of articles that go into great detail about the pitfalls and downsides of using NOLOCK with as few as a thousand rows.
Seems to me that sort of suggests that hint has no real usage in almost every situation.
That's not usually true if anyone is modifying any row in the table. Or particularly when multiple people are modifying table rows at the same time. In those cases, NOLOCK can significantly improve response time, since it doesn't have to wait on any locks.
Now, as I noted in my original post, that does not mean that is should even most often be used, let alone "always". But to deny that NOLOCK can -- and does -- speed up processing in many cases is just plain false.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply