I’ve already covered how NOLOCK gives you random results when you’re querying data that’s changing, and that’s a really powerful demo to show folks who think NOLOCK is safe to use in production. However, I’ve gotten a question from several users:
But I’m querying data that isn’t changing – sure, OTHER rows in the table are changing, but not the rows I’m querying. Isn’t NOLOCK okay if I’m querying stable rows?
Nope, not even close. Get any Stack Overflow database, and start with the query from the last post – except this time we’ll modify it so that we’re updating some of the users, and querying others. Start by dropping your indexes just so you get consistent results:
|
1 |
EXEC DropIndexes; |
One of the more common DisplayNames is Alex. In one window, I’ll get a count of the number of Alexes:
|
1 2 3 4 |
SELECT COUNT(*) FROM dbo.Users WITH (NOLOCK) WHERE DisplayName = 'alex'; GO 20 |
And in the other window, I’m going to set everyone’s location and website – EXCEPT the Alexes, who I’m not going to touch:
|
1 2 3 4 5 6 |
BEGIN TRAN UPDATE dbo.Users SET Location = N'The Derek Zoolander School for Kids Who Can''t Read Good and Want to Do Other Stuff Good Too', WebsiteUrl = N'https://www.youtube.com/watch?v=NQ-8IuUkJJc' WHERE DisplayName <> 'alex'; GO |
Watch disaster unfurl:
The number of users named Alex appears to keep changing – EVEN THOUGH I’M NOT UPDATING THOSE ROWS! The reason: the location of Alex’s rows may be moved around by things that are happening to other rows.
I can’t emphasize this enough: with NOLOCK, you can:
- See rows twice
- Skip rows altogether
- See data that was never committed
- And have your query fail with an error
If you’re cool with that, great – NOLOCK is for you. If not, it’s time to consider other ways to get the performance you want while still getting the data accuracy that your users require.
If you want to rerun the demo repeatedly, you’ll need to rebuild the table between passes:
|
1 |
ALTER TABLE dbo.Users REBUILD; |
112 Comments. Leave new
this looks like I should never use nolock.. ever again..
With the possible exception of loading a bunch of lookup tables into a set of drop downs, there is no reason to use nolock. Ever. Period.
> loading a bunch of lookup tables into a set of drop downs
How much more performance do you expect to get w/ NOLOCK here?
I would be astounded if there would be any measurable difference at all.
It really should only be used on flat or nearly flat, static data, that doesn’t update itself – like constructed reporting tables that are built through an ETL process and are only updated every few hours.
Likely very little.
I’ve seen my fair share of NOLOCK, and I’ve fought quite a few battles trying to sway developers away from it. Telling devs “with nolock is bad” can meet resistance. Instead, I normally start with recommending they use STILRU. Set transaction isolation level read uncommitted. I pitch it by saying, it’s like writing with no lock everywhere and you only have to state it once. Of course, it’s not truly the same, but it normally nets the same *cough* benefits and it get the devs thinking about isolation levels. Later on, after we break the with nolock habit, we can have an earnest conversation about transaction isolation and perhaps discuss other transaction levels that are less likely to read dirty data and block less then the default transaction level. It’s a nice toe in the water that doesn’t completely remove their “with nolock” power.
Wow! It’s a social engineering approach! 😉
This drives me insane to absolutely no end. When asked about using a nolock hint, I used to answer that it is ok if none of the tables are being modified actively – but then would get used all over the place. Recently I have been telling analysts that its only ok if they know all the data in a database is static, and if they are at all uncertain, with even the most infinitesimal amount of doubt, that they need to use read committed. But use of the nolock hint still seems to replicate like extremely horny cats.
I have a third party application that uses a bunch of nested views that someone took it upon themselves to modify all the views to use nolock hints, in an EXTREMELY active OLTP application. Sometimes our ETL job that pulls data out of it for regular use will have to query it 10+ times to get clean data. When I tried to undo it, a bunch of reports started breaking due to horrendous queries that relied on the nolock to even run. All the while our SQL developer has more than 2 years of work on his plate with critical organizational compliance issues and the organization almost died when they saw the quote for 2500 hours of consulting work the fix all the reports. :-@
I really wish MS would provide a way to make SQL ignore nolock hints the way Postgres does, or add an option to make the query optimizer instead rcsi as someone on the 2019 thread suggested.
If the tables aren’t being modified at all I doubt there would be much if any benefit. By definition it isn’t going to encounter blocking from modifications. Sql Server can already skip taking S locks at read committed in this type of case. https://www.sql.kiwi/2010/11/read-committed-shared-locks-and-rollbacks.html. Only other benefit I can think of would be that it allows allocation ordered scan to be considered.
It skips taking out the shared lock though, which takes a small amount of time. We had a report which would get run about 40 thousand times per day, 90+% of which was in a period of about 6-7 hours. It did have a noticeable (but small) difference before the analysts finally enabled caching after discovering the ‘live’ data behind it wasn’t actually live and fewer than .1% of the executions were actually querying new data.
“It skips taking out the shared lock though, which takes a small amount of time”. Did you look at the link? SQL server doesn’t need NOLOCK to do that in the case the object isn’t being modified
I totally missed that section! That is extremely interesting. The rollback section makes more sense to me now as it seemed weird he was emphasizing something that at the time was obvious.
I’m curious why that hint did anything, and even more curious whatever else the analysts may have done without a change request.
Thank you for sharing this….essentially the nolock hint is entirely worthless unless you are in a condition where you are going to get some bad data back and you don’t care.
traceflag 8755 disables ALL locking hints
does this work? you seem to have found otherwise here https://ask.sqlservercentral.com/questions/139355/traceflag-8755-not-working-anymore.html
Ah, forgot this one. Did some testing again (SQL2019 CU2) and indeed this traceflag seems not to work (anymore). Tested both with session enabled option (DBCC TRACEON(8755, -1) ) and as startup parameter. Updated a table in session 1 without a commit and tried to read the table with (nolock) in another session. It dit NOT ignore the nolock hint.
The only reason that a view could “depend on” a (nolock) hint is because the activity on your system is high enough that the locks imposed by committed reads is causing blocking and being blocked.
You may be able to rewrite the queries to view much less data but, in the absence of that, why not let the (nolock) hints do their thing ? Actually check whether the queries are being corrupted by the (nolock) hints or not.
Most of the time, the issue with (nolock) hints is that the records will be accessed at different times and so some of the data will have already been updated and some will not.
Normally, this isn’t really a problem – if you’d run the query a minute later, you’d have gotten different results anyway. Usually, the time that it is a problem is when the data is inter-related, especially with transfers – If Fred transfers $100 to Bill, you don’t want the query to catch Fred’s debit without including Bill’s credit or vice versa.
whether using NOLOCK is ok highly depends on the situation and it doesn’t actually have all that much to do with whether data is changing. you have to ask yourself, if the data is changing, what is changing about it, and is it absolutely critical that whatever or whoever is using this data never see an inconsistent view of the data to what is in the process of being committed? for instance, i might have a view that lists the status of particular service appointments. in the same instant that it is being read, some of those service appointments may be cancelled. but is it ok if i don’t show the attendees of that appointment that it is cancelled until the next refresh? it absolutely is ok, because they will be apprised of the new status at the next refresh.
think hard about this. absolute data consistency is great but it is hardly ever critical to the application you’re building. there are some cases – financial primarily – where it is, but people need to learn to think critically about their use case and what is and isn’t a necessary trade-off.
To be fair this type of anomaly can also happen at read committed isolation level (locking style rather than RCSI) as well as at read uncommitted level. It is not unique to NOLOCK.
At pessimistic read committed level locks are taken and released as and when the rows are read so there is opportunity for concurrent activity to move them around during the scan.
Yeah, but it is much more difficult for it to happen. I only have one app that is active enough to cause data quality problems for our purposes, but using a higher isolation level becomes disruptive to the app when both the app and reports are busy. In that case, data is just getting entered as it comes in, and is known to be incomplete or inaccurate initially until more discovery is done and its really only important for the data to be very clean after the data has left it’s active state and stabilizes. Technically, its data can change for as long as 2 or 3 years after it was initially entered, but virtually all of the change happens within the first 3-5 days and its almost unheard of for anything to change after about a month.
There are 2-3 other apps we have where I am sure it happens as well, but in those each record has a record status column, and when the record is active, the data in the record itself is known to be of suspect quality until the status changes to posted or locked or whatever, indicating that it wont be changed any further.
I would think if you needed queries to be that clean at a level of data activity that would cause dirty reads at any level of frequency, you would need to offload those queries onto a replica where it can better isolate transactions without hurting the app.
I will follow this discussion with great interest. I have used nolock after discussing with stakeholders both the risks and the benefits. In a number of years running dozens of SPs that use nolock against an accounting app experiencing frequent updates througout the workday, we never did see any evidence (complaints from users, inconsistent reports, etc.) of read anomalies. We all know they can happen, but we never did see them live. Perhaps this is because the updates were always just a very few rows out of 30 million rows, representing one transaction? Not “bulk” updates at any time during business day as per Brent’s test? I wonder what the likelihood of read anomalies really is with very tiny row updates relative to table row counts.
You could probably calculate the odds, but however small the odds are, it would only take one bad read to have a vendor get paid a 6 figure bill twice (or more), or have payroll look cheaper than it does because some of the records were skipped, to have an employee get paid on a million dollar salary when someone accidentally put the trailing zeroes to the left of the decimal, or for your book keeper to not pay all the bills, or pay them incorrectly from a report that was wrong.
Of course, we throught of that. We were careful to be sure those queries were not responsible for billings. They are “overview” queries designed to give an overall estimate of office “status” comparitive to other offices. Not transactional and not used for critical business decisions.
Our app is like yours. OLTP and query single rows and update single rows.
When I showed the devs Brent’s last demo, they said “but we don’t do mass updates like that”.
I need to make a test that updates row with key=N and query row with key=N+1 and show that the update can mess up the query. Need some way to keep the two SPID in sync on N…
I have heard a few times over the years to use NOLOCK when querying transactional data; essentially that it provides optimal means to avoid “interfering” with an ongoing simultaneous Transaction. Any sound reasoning to this?
If you don’t want to interfere with the write transaction, yes, but that is the absolute best way to get dirty data, or just have a query completely fail. If the quality of the data doesn’t matter that much for whatever purpose you are using the nolock, that can be fine, but I’d be extremely suspicious of any thoughts that it would be ok – the way you think the data is used can change over time, or even just have user expectations change, when they ‘forget’ the data is supposed to be regarded as dirty.
The only place I really like NOLOCK is when I’ve got a long-running process inside of a transaction and I want to keep tabs on its progress. (How many rows inserted or updated? How many invoices generated?) Of course,
exact results are not critical and I’m the only one affected, so that makes it OK.
+1. I do this all the time. Only place I can think that I’ve ever used it though.
Really interesting example, thanks. READ UNCOMMITTED was introduced by my old boss as part of our coding standards on the basis that it gave a performance boost, and given the nature of the nature of the system we were supporting, there was little risk.
From that point I always considered the risk with NOLOCK to be limited to how likely it is for a transaction to be rolled back (and therefore you read dirty data). If that risk is very small, then the (perceived) performance benefit was worth it. However I hadn’t considered that you could actually see the same row twice!
I’ve recently started testing with Read Committed Snaphot (RCSI) as an alternate way of preventing update locks taken by replication from slowing down my reads in a reporting environment. RCSI comes with it’s own considerations of course (namely tempdb growth, potential long version chains slowing down SELECTs), however for most systems this is the more sensible way of tacking the problem, providing you test of course!
also, non-matching rows may be returned (when you shuffle those rows, how do you know ‘Alex’ is still in that slot?)
NOLOCK hints are fine in a world of data warehousing for reporting tables that are reloaded nightly. I’ve seen significant performance gains in a well-used data warehouse from using them. However, totally agree with Brent that I’d never use them in the application (OLTP) world for all the good reasons he spells out.
DW – if you’re referring to reports that run during the day when data isn’t changing, why would NOLOCK help you at all?
If you’re referring to environments where querying happens at the same time you’re loading data, might wanna reread the post a little more carefully.
I believe he’s referring to a DW refresh that happens off-hours, when there are no updates happening.
So then…why use NOLOCK? What’s the point?
Please don’t patronise me, I read the post fine. This article, while old, demonstrates the sort of gains that I was talking about: https://www.sqlservercentral.com/articles/the-effect-of-nolock-on-performance. While it may no longer be true in more recent optimisers and with better servers, the advent of columnstore and so forth, it does demonstrate that it was considered worth doing. If you have a link to any evidence that it is no longer worth doing then I’ll gladly read it. Thanks.
Your best bet there is to note the author’s repeated cautions at the top of his very own post telling you not to do it for performance reasons.
DW Dev Dude – I only learned this from a comment in this post as well, but if the data isn’t changing, SQL doesn’t take out a lock at all and nolock doesn’t do anything. I have had analysts work on reports (that use static data) that ran better after they claimed they only added nolock hints, but these may have been from them changing other stuff in the report. I’ve had them go in and tinker with stuff quietly before.
Until this post I had always believed that SQL always took out an S lock at the standard isolation level, but it seems at least in 2008 and up, that is no longer the case. Since your article test was done using 2005, it is possible that S locks were still taken on reads at that point, when the data isn’t changing, but no longer.
Now, the only real performance gain you can get by using nolock is when the data is changing and you are probably going to get back dirty data.
Wondering if setting the database compat level to 2005 would allow reproduction of what was talked about in his post…I may play with that if I have a sample database I can set that low somewhere
Locking isn’t the only difference. With NOLOCK an allocation ordered scan can be used which may have better performance in some situations. https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/11/08/when-can-allocation-order-scans-be-used/.
This may be a factor in the linked SSC example, at some point I’ll probably have a look at it to see if this is still reproducible, and if so what exactly is going on there. But not this week as am on vacation.
But if the data isn’t changing, why would it matter? the index is already going to be ordered assuming it is a copy that is constructed for reporting. If its the live copy, you’re just hoping that no one is writing to the data outside of normal hours or that the report or whatever only ever gets run during the off hours
Actually I see that you were the one that shared the link about skipping the locks before. Would be curious what you thought – I would think that if the index is already in order that even if it does an unordered scan against an index that is already in order, it wouldn’t be jumping around and effectively do an ordered scan from disk
I wonder if the same error would occur if sum(1) or count(displayname) was used instead of count(*)
Yep, same plan.
> And have your query fail with an error
That is kind of scary. In what situations query with nolock may fail with error?
I’ll leave that one for another post, but the error is, “could not continue scan with nolock due to data movement.”
Funny, I have never seen the “could not continue scan with nolock due to data movement” error, and I have debugged & modified hundreds of SQL stored procedures in my production environment that use it. Maybe I don’t know where to look for the error. Oh, and “Funny” meaning scary, not “ha ha.”
That is creepy Brent… I read the blog and suddenly a data warehouse extract from a busy OLTP system started to fail… NOLOCK duplicate key galore. We’re looking into rcsi, but that also comes with some drawbacks (step 1, is that even enabled on the source?).
Power BI direct query by the way… does not support ‘set transactional level x’ statements (nor CTE’s, like wut?). I have not figured out yet how to handle that… Should look into the connection string, but seeming how Power BI supports direct query I guess there are no options other than NOLOCK.
Yeah, if you’re selecting me, you better lock well! 😀
We have a vendor that uses NOLOCK everywhere – every single query in every procedure. Reading their code makes my stomach hurt. In your post, you mention the location of rows changing. Is the risk of NOLOCK reduced (or maybe eliminated) if the schema is consistently clustering on identity primary keys? Would this keep rows from moving?
Tom – you mean like the Users table that I use in the demo above?
There are tons of tables. Each has a clustered PK on an identity column. Every query to every table uses NOLOCK. Is that what you meant?
No, I meant that the very table I use in this post is clustered on an identity. You may want to download the database and run the demo yourself before following up with more questions. That’s why I work so hard to give y’all the exact database and demo scripts. Hope that helps!
Oh, thanks! I should have looked more carefully at your post. Overlooking things is what happens when we have too much to do in too little time 😉 That’s my story anyway!
I have a baaaaaad feeling that if I modified every stored procedure to take out the NOLOCK in every SELECT statement, the company where I’m employed would experience locking issues that would cripple productivity and make the users hate our IT group even more than they do now. Ugh.
Very aggressive, radicalized statement. One can turn everything into an absurd taking it out of context as with this post. Actual example is good one, but hardly a justification of “never ever do it”.
Vlad – the post doesn’t say “never ever do it.” Reread it a little more closely. Thanks!
Thanks for this. You hit the nail on the head of what my assumption has been for 15 years – that as long as the rows that I was pulling back in my query weren’t changing I was okay….. which is not the case at all.
You’re doing consulting work for our company right now and my boss made mention of this along with your other advice about functions crippling the optimizer and creating a thread bottleneck.
Thanks for the help, reading through a bunch of your stuff now to see what else I think I have a handle on but actually don’t!
You’re welcome!
I said it once in a meeting to don’t use NoLock and the Solution Archetict looked at me i was scared for life. It’s used every where even in SPs. Even the DBA handed over the DBs to me asked me strickly to never run anything without a NoLOCK and to be included in all code. But I listen to Brent Ozar 🙂
Okay, I’m the idiot that you’re all talking to. I use (nolock) hints all the time. And, I have never once found an actual problem caused by them (though, to be fair, in most cases I wouldn’t know about a problem because the data would be marginally off and no-one would notice).
However, I have experienced a LOT of problems when I was not using (nolock) hints;
Production systems that had “red bars” indicating a blocked transaction that lasted for minutes.
Reports running from Production (because they needed up-to-date information or because there was no reporting database) that took hours to run but, once the (nolock) hints were added, ran in minutes.
Corrupted data in the database because the application was using implicit transactions and step 1 succeeded but step 2 got blocked by a report and failed.
Much of the reason I need the (nolock) hints is because the application is badly written and does not use transactions properly or because it is pulling more data than it needs. I particularly despise the Access style of database application where the current record is being updated in the database as it is being changed… a lock is being held for the minutes/hours/days that the user has that screen open.
Requiring that reports be run from a secondary Reporting Database is a lousy idea. You’re running two databases with, potentially, twice the cost. If you could run a single database with twice the power, wouldn’t that be better ? In theory, when you are running a reporting database, you can optimize it for reporting – perhaps have a completely different set of indices, for example. But who does this especially if you are using replication, log shipping or another tool to allow (near) real-time reporting ?
A Reporting Database COULD provide a prompt failover system but most failures aren’t because the Database server crashed… they’re because the Internet went down or the power went out or because the system is too slow and the users are sick of waiting for a response. Having a hot spare is a huge, almost always unnecessary expense. And, psychologically… I have found that, when something goes wrong, it is usually better to fix it slowly than to fix it quickly. The quick fix often makes things worse and sometimes makes a good fix impossible because people who are rushing are much more likely to make mistakes.
I would really prefer an All Nolock database… where you read the data without any locks and then, when you want to update it, you basically run “UPDATE Person SET LastName = WHERE Person_ID = xxx AND LastName = “. This would then fail (well, would update 0 records) if someone else has changed that data and so the application would prompt the user how to proceed… still make the change, accept the other change, make a new change based on the new version of the data. (Yeah, I know it would actually be holding locks during the updates but these would be much quicker and less frequent.)
In the meantime, I know from my own experience that (nolock) hints are faster, sometimes dramatically so. And I know from that same experience that, partly because of this performance improvement, real world applications using (nolock) hints have LESS corruption or failed transactions than those that use Committed Reads.
Maybe I have just been working with a lot of badly written applications. Maybe, if they were written better, I would be confronted by more of the possible issues that arise from using (nolock) hints instead of those issues being swamped by the benefits. I get that. But I cannot control the quality of the applications. All I can do is make them as functional as possible.
A final note… I do not like using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. The (nolock) hints are tied to the specific tables and also work on dynamic SQL that is then executed. I want the programmer to be actively thinking about the isolation level that they are using rather than relying on an assumption that it has been set correctly.
Simon – sure, to learn how to fix those issues and get fast, accurate query results, check out our Mastering classes. Enjoy!
Note: In the example code my intention was for it to only update the LastName field with the new value if the current value in the database matches what the LastName was when the record was originally read. The system “fixed” my attempt to illustrate this more elegantly.
I wish more devs from my office read and agreed with this post, Brent. I’m pretty sure I can count on one finger the total number of times I’ve used READ UNCOMMITTED. I just don’t see the need for it with what our devs do.
I am getting sporadic blocking because the application has a “SELECT COUNT(*) FROM Order_ WHERE…” query. It only seems to happen once or twice a day (and I don’t understand the indices or, more importantly, lack thereof, being used by the estimated plan) so I suspect this is mostly a glitch but, if the query included the (nolock) hint, it would not be causing these blocks.
Sure the number returned by the query might be slightly off if an update were in progress, but it can vary from one second to the next legitimately, so the blocking is more of a problem in my opinion.
Simon – or, you could just use indexes or the right isolation level.
Or, I guess you could let your users get random results, if you’re into that kind of thing. Me? I’d use indexes and the right isolation level.
As far as I can tell, I am using the right indexes. The two main conditions are on Person_ID and on Create_Timestamp but, at least occasionally, the query plan is using the index for the timestamp and then merging that with a Heap search for the Person_ID despite having a number of Person_ID indices.
Thus, Indices aren’t always a solution and I have literally never seen an example in this database where the result of a query is wrong because of the (nolock) while I have seen a number of cases where users disconnected because of a 10 minute block that affecting tens of users and this had definite impacts on the data and on the user satisfaction.
Heap? Hoo, boy – I would say you’re not using the right indexes.
Doing a custom query tuning project is a little outside of the scope of what I can do in a blog post comment, but if you want to learn more, check out my Mastering Index Tuning & Mastering Query Tuning classes, or hit Consulting at the top of the page. Hope that helps!
Thank you for this. interestingly, at work, I have reading access to a production server – my coworker always said be sure to use nolock so it doesn’t prevent data being produced in the production server – is that true?
I don’t use nolock and they keep saying it’s a big no-no, but im skeptical, what are you thought? will NOT USING nolock really f the production server up?
I can only talk about my experience. If you are running a query that is looking at more than a few records on your Production Database, that query should use (nolock) hints because otherwise it can block updates, possibly causing them to fail.
This is especially true if you are running reports on Production. If the report runs in under a minute, you’re probably fine but, if it takes half an hour to run, it could be blocking updates for that whole time and that is not going to make anyone using the system happy.
Also, the (nolock) hints might allow your report to run in under a minute even if it did originally take half an hour – If your report isn’t locking records or checking for locks, it can run faster itself. Plus, your system doesn’t have other users running reports that are blocking your report.
I understand, “Don’t run reports on Production !” I totally get it. But some people don’t have a choice and, for them, the (nolock) hints allow a report that could take hours to run, to run much faster and with a minimal impact on the performance of the system for everyone else.
Could the report be wrong, if you use (nolock) hints ? Yes, it could. But it won’t be wrong because the data you are looking at for big reports usually doesn’t change that often – the errors happen when the data on the report changes from when the report starts to when it finishes so, if the report runs in under a minute, how much scope for errors is there ?
And that assumes that the report cares about errors. If you’re querying which patients have been diagnosed with Asthma and Diabetes, are overweight, and take Aspirin, you’re going to get exactly the same list whether you use (nolock) hints or not. If you are checking which user looked at which patient’s chart, you will get exactly the same results whether you use (nolock) hints or not. If you are checking how many active patients are on the system, the number could change from one run to the next whether you are using (nolock) hints or not, so who cares which number your report shows ? (We get new patients all the time and, occasionally, a patient dies… so the number changes throughout the day.)
NOLOCK doesn’t prevent your statement from taking locks. That is a common misconception and is provable false. NOLOCK will prevent your statement from being blocked by other locks. Erik Darling wrote a nice blog on this https://www.erikdarlingdata.com/sql-server/isolation-level-locking/
What I hear from your post is that your are using NOLOCK as a performance booster. It’s not. While some of the cases you use may very well be a good argument for using NOLOCK, I suspect that there needs to be further investigation as to the causes of poor performance.
I’m the DBA. Which, in this context, means the guy running ad-hoc queries and trying to keep the system running smoothly. I don’t have any control over the application code so I cannot force it to use proper transactions or even improve the joins.
All I can do is ensure that the code I write works as accurately, as quickly, and with as little impact on anything else, as possible.
The main benefit of (nolock) hints is that it dramatically reduces the impact of queries on each other if they mostly use the (nolock) hints.
Because of the way SQL works, most updates affect individual records while most selects affect multiple records, at least fleetingly. This means that select statements without a (nolock) hint can inadvertantly block an update even if the select isn’t going to include the updated record – it may not have been filtered out of the result set yet.
The performance improvement of using the (nolock) hint is 95% because the system WITHOUT the (nolock) hints is badly optimized and is running queries much slower than it should. The (nolock) hints don’t make it faster, the lack of them makes it slower. The system COULD benefit from further investigation, but I have no way to do this because I don’t control the application. What I can do is include (nolock) hints in my queries and, when I do this, those queries work dramatically faster without any apparent loss in accuracy.
(nolock) hints do perform some locking, but these are usually either table locks or latches, both of which are much faster than “real” locks and, because they are shared locks, they do not interfere with other queries that also lock the data.
Perhaps consider READ COMMITTED SNAPSHOT isolation level.
NOLOCK is fine providing you don’t particularly care about the integrity of the data you’re returning e.g. a COUNT statement that counts the same row twice.
An Interesting read, but I have different take. I think the demo and the discussion demonstrates why NOLOCK (READ COMMITTED) should be used everywhere. It is ignored when inappropriate and significantly reduces impact from poor performing queries. Guess What, Data Changes!
The real thing to change is the attitude that what is happening is relevant to any query against more than one row.
Stop reporting against your OLTP Environment. If your database is so busy that you can see significant differences in your results, any result set is out of date in the same amount of time regardless of indexes or isolation, It is the nature of data.
Hmm, I’m guessing you haven’t seen Read Committed Snapshot Isolation or Snapshot Isolation. They’re much better ways to do reporting in production, *and* see consistent results. I cover ’em in my Mastering Server Tuning classes – check out the module on isolation levels. Cheers!
I, Am very interested. Because I will actually use that information. But (1) if those tweaks fix everything, why are they not the defaults. Everything has a cost and energy is conserved.
But (2) when dealing with hundreds of incompetent developers who have no interest in understanding anything about the database engine itself We have to dive to the least painful path.
Those are great questions, and you can guess where I answer ’em – in class. See you there!
Many thanks for great explanation in details. I have a question about what differences between nolock and with(nolock). And if I use “unread committed isolation level then the result will get better ?
Those hints are all the same.
As I understand it, when the WITH keyword is left off, the compiler ignores the NOLOCK.
WITH (NOLOCK) hint is better than without the WITH.
Two main reasons – firstly, just (NOLOCK) is deprecated, so you’re just accruing technical debt and secondly, if you ever forget the brackets, WITH NOLOCK will just error while NOLOCK will simply be seen as a table alias and get locked.
Throw in that you can “stack” multiple hints with a comma inside the brackets and, while the basic functionality of WITH (NOLOCK) vs (NOLOCK) is the same, using WITH (NOLOCK) is safer and better.
“(nolock)” and “WITH (nolock)” are the same except that Microsoft officially requires the “WITH” and keeps promising to deprecate the option of skipping it. If this happens, any scripts without the “WITH” will stop working.
The “set transaction isolation level read uncommitted” statement is functionally the same as using the (nolock) hints except that it does not allow you to select which tables should have the dirty reads… If you have a query that includes one actively updated table among ten lookup tables, you could potentially keep the actively updated table using committed reads while the others (arguably) could live with dirty reads.
The “set transaction isolation level read uncommitted” statement also only applies to the current execution level… if you create a dynamic SQL script and execute that, it will NOT inherit the parent’s transaction level. As such, if you are going to use dirty reads, I think that it is better to get in the habit of doing it explicitly using the (nolock) hints.
Thanks for your detailed explanation !
I agree, (NOLOCK) / Read Uncommitted (RU) is fine when used appropriately. Yeah, don’t read critical amounts with it, for example, but for most other things it’s ok. Btw, as noted by at least one person above, Read Committed can *also* read a row twice, although it’s less common than for RU.
>> When people see the “nolock” hint, they often assume that means their query won’t take any locks. What it really means is that it will ignore locks taken by other queries. <> Hmm, I’m guessing you haven’t seen Read Committed Snapshot Isolation or Snapshot Isolation. <<
Oh, I've seen it. But the overhead is *huge*. All modified data copied to tempdb, *14 extra bytes per row*, and so on. If you enable RCSI, be prepared to spend lots of time afterward defragmenting your major tables, otherwise all those page splits could really devastate your performance.
It scrambled up my comments, I guess the gt gt means something special to the editor reading the comments.
When people see the “nolock” hint, they often assume that means their query won’t take any locks. What it really means is that it will ignore locks taken by other queries.
And how does it do that? By not taking shared locks itself! Yes, it may have to take schema locks and other very high-level locks, as Simon H mentioned, but in fact it does *not take locks* on every row as RC requires.
This thread! bunch of DBAs feeding each other on poor practices, as if everyone’s shit here is pristine and well backed up. I wonder why DBAs get fired left and right every time there is a system glitch.
DBAs generally don’t use NO LOCK.
They just get mad when we don’t use it on untested queries in production and lock up a table (and replication) causing them to go down a rabbit hole to find my bad query.
*Also, in my 25 years I’ve never seen an experienced DBA get fired…..even a few that should’ve been for HR reasons and working about 30 mins a day.
Hi Brent, great explanations. So what’s the goal of “nolock”? why MS engineers included it? Why does it exist? just to save about/as much 10% of response time for a given query (because of the absense of overhead needed to aquire the locks) ? is there any situation where “nolock” would be recommended ? there should be a good reason for it to exists…. right? in every place I work someone tells me “you forgot the nolocks”, as if it were mandatory with the sentence “select … from … nolock”, …. that’s really bothering
Read this post: https://www.brentozar.com/archive/2021/11/nolock-is-bad-and-you-probably-shouldnt-use-it/
Your project paid mega bucks for a DBMS that is always consistent.
SQL cares so much about consistency, that if a rollback fails it will reboot instead of being inconsistent.
Now you are going to use NOLOCK and tell it you do not care about consistency.
I suggest looking at snapshot isolation.
Mostly a copy of something I posted in response to something else but maybe it answers your specific question – why do people use (and often insist on using) nolocks;
(nolock) hints are a way to read lots of data on a production database without hurting the users that are trying to perform updates – basically to run reports. They do NOT guarantee that the data is correct. However, in practical use, the data usually is correct and the (nolock) hints prevent the other users being blocked or deadlocked by the user running the report.
In my experience, the main application is usually provided by a specialist vendor and, as a user at a client site, I cannot control how well the application handles deadlocks so there is a hefty chance that data will be lost if a deadlock occurs. What I CAN control is whether the reports I run on Production make deadlocks more or less likely to occur. (nolock) hints reduce the chance of deadlocks and so protect the integrity of the data.
It would be better to run the reports on a non-Production database, but that means that the reports would be guaranteed to be out of date. Running them on Production with the (nolock) hints is the best balance I have found.
Brent, and many other top-line DBAs despise (nolock) hints because they do have the potential to return garbage and users may make decisions based on that garbage. If they are the vendor writing the application, I agree with them – build proper transactions and deadlock handling into the application so that (nolock) hints are not needed.
But that isn’t the world I code in.
The definition of NO LOCK has changed Dramatically from Version 6 to date and behaves differently based on the instance configuration. That is why it is DEPRECATED.
1. How do you think NOLOCK changed from version to version?
2. How do you think it behaves differently based on instance configuration?
3. Why do you think it’s deprecated?
1) https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-ver16
NOLOCK
Is equivalent to READUNCOMMITTED. For more information, see READUNCOMMITTED later in this topic.
For UPDATE or DELETE statements: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Again, please answer the question to support your statements:
1. How do you think NOLOCK changed from version to version?
2. How do you think it behaves differently based on instance configuration?
3. Why do you think it’s deprecated?
The only one you answered was 3, and that’s only in relation to its usage in UPDATE and DELETE. NOLOCK itself hasn’t been deprecated.
I’m a big fan of (nolock) hints (or READ UNCOMMITTED or DIRTY READS or whatever you want to call them) when I am writing informational queries – Basically SELECT statements when the query is not being used to update data directly.
Anyone that uses a (nolock) hint on an UPDATE or DELETE statement should have their DBA card revoked for life.
(nolock) hints are a way to read lots of data on a production database without hurting the users that are trying to perform updates – basically to run reports. They do NOT guarantee that the data is correct. However, in practical use, the data usually is correct and the (nolock) hints prevent the other users being blocked or deadlocked by the user running the report.
In my experience, the main application is usually provided by a specialist vendor and, as a user at a client site, I cannot control how well the application handles deadlocks so there is a hefty chance that data will be lost if a deadlock occurs. What I CAN control is whether the reports I run on Production make deadlocks more or less likely to occur. (nolock) hints reduce the chance of deadlocks and so protect the integrity of the data.
It might be better to run the reports on a non-Production database, but that means that the reports would be guaranteed to be out of date. Running them on Production with the (nolock) hints is the best balance I have found.
But not on UPDATE or DELETE statements. That’s crazy.
Simon – check out Snapshot Isolation. It requires no vendor app modifications. We explain it in the isolation levels section in Mastering Server Tuning.
Nolock on update and delete has always been silently ignored. duh
I doubt they will ever start throwing errors on its use, just it is now = READUNCOMMITTED
So, the answers to 1 and 2 start with, what does READUNCOMMITTED mean and how have those behaviors changed
Right, exactly – and YOU are the one who said it changed, so YOU are the one who needs to answer that question.
Further replies without answering the question will not be productive.
There are valid used cases for READ UNCOMMITED.
For example, when incrementally copying out an Event table from an applications system, usually to support downstream reporting without clattering the Application.
In that case I would gather an end point e.g. SELECT @NewId = MAX(Id) FROM app.EventTable;
And then pull my latest chunk of data using
Select data FROM app.EventTable WHERE IS > @oldId AND Id <= @NewId;
In this case READ UNCOMMITTED would avoid locking out the incoming data and you are protected from dirty reads by the endpoint.
So yes, the risks it poses are there, and it should not be the default option. But there are cases where it is useful.
Peter – what do you need the max ID for?
If you’re just getting the max, all you need is:
Select data FROM app.EventTable WHERE IS > @oldId
Presto, you’re done, and no need for dirty reads – plus your query goes faster! You’re welcome. Don’t feel bad – I’m a trained professional, and this is what I do for a living.
If you have other cases where you think you need dirty reads, feel free to click Consulting at the top of the screen, and I’d be glad to help tune more of your code in private. I’m sure you wouldn’t want me repeatedly de-pantsing you in public, right?
I mean, if you’re into that kind of thing, we can even role play while we’re consulting together. I can tell you what a bad boy you are for writing that code. I don’t even charge extra for that.
Hi Brent, we are using Microsoft SQL Azure (RTM) – 12.0.2000.8 and a developers wants to put NOLOCK on all the tables in all the current stored procedures we use for reporting. Would this be a valid case to use NOLOCK in order to get some faster processing of our SPs??
The developer’s comment: “Disadvantages: Data consistency Issues – , as it allows reading uncommitted data (dirty reads) in some cases.
” – , but in our database the data in the dbo schemas (Fact and Dim tables) are static. The ETL updates the data in the staging schema then there is a schema swap that happens at the end from staging to dbo. Thus the FACT and DIM tables in our database is static. So the disadvantages of using WITH(NOLOCK) does not apply to it
as there will not be uncommitted data, unless someone is doing a manual update on the data you are looking at currently (that they are not allowed to do anyways).
Have they proved that NOLOCK is faster?
Thank you for the response Brent. The developer is doing it as a matter of practice as our tables are heavily used almost around the clock — and thinks it will improve performance when a long running query locks a table trying to be accessed by another table. The developer thinks because of the schema swap that means the table are static – though the data in the tables changes daily.
It’s a fairly straightforward question, but I’ll repeat it: “Have they proved that NOLOCK is faster?”
Until that question is answered, further requests aren’t really useful for either of us. Fair?
Point taken Brent. Yes. Fair. Thank you.
A very long time ago – pre SQL Server 2008 – I made my bones at my current job by creating Views that included (nolock) hints and replacing the tables with the equivalent view in the 100s of Crystal reports that were being run against a Production database.
Previously, the users of the application regularly had “red bars” which indicated failed transactions and the reports were taking hours to run.
After I made the changes, there were almost no red bars and the reports mostly ran in seconds.
The application was badly written but I had no way to fix it. There was no reporting database, but I didn’t have the money to create one, and many of the reports “required” being run against live data to avoid discrepancies.
I had to work with what was available and the (nolock) hints were like magic for the users. They didn’t even realize how bad the system was until I “fixed” it.
Sure, there is the chance that some of the reports were not 100% accurate because of the dirty reads. But there was a guarantee that some of the updates were failing before that and no certainty that the failed transactions were re-entered correctly.
At the time, I proved decisively that (nolock) was dramatically faster than running the same crystal reports without it on a live system with 100s of concurrent users.
I can’t guarantee that the same difference would apply to a well-written application, or on a reporting database that does not have active transactions running simultaneously, or on a system that only has the occasional report run against it.
I have literally never been alerted to a query using (nolock) hints that is producing incorrect information, either. I know it can happen, but I know that there are other problems that can also happen and are much more common: Very few database applications use transactions and error handling properly.
I do what I can to fix the things I can control. (nolock) hints are ONE of the tools that I use.
Another tool is reviewing the code so that a GUID is linked to a VARCHAR the right way around, or is converted explicitly. Another is pulling data into a Temporary Table if the same data is being used by multiple queries. If appropriate, I’ll even build an index on that Temporary Table. When using Linked Databases, ALWAYS pull the majority of the data. Keep the Database CPU around 50% during the busiest times, use SSDs, have enough memory, preallocate large enough TempDB files, setup alerts when disk space runs low, have a big garbage file on your Log File Drive so that, if the Log files fill the disk, you can delete the spare file and recover enough space to function long enough to fix the problem. I’m sure I’m missing plenty of others, but most of them are the product of experience – My experiences having messed up; The most effective teacher.
Thank you Simon for your experience in the field. We are a team that used to work in our own silos and with our own understanding of best practices and now being asked to merge it all together.
Why not snapshot isolation or RCSI? Those would get you accurate reports while avoiding the blocking problems.
Thanks Brent — I checked and we are using that in Azure. So from what I am reading on the Microsoft site — that should avoid blocking problems including ones from report queries blocking other report queries, right?
Bingo. In that case, the only thing NOLOCK is doing is purposely giving you dirty data – when there’s already a nice, clean, legit copy waiting for you in the version store.
And if your queries didn’t have the NOLOCK hint, then they’d be getting that nice, clean copy.
But when folks put in NOLOCK, they specifically tell SQL Server, “No no, give it to me dirty, just the way I like it.” That’s the terrible thing about folks like Simon going around dispensing advice. (sigh)
Thanks again Brent!
We use in our environment READ COMMITED SNAPSHOT (RCSI) and that is running perfect in the test environment with SQL Server 2014.
But on live (SQL Managed Instance Azure with SQL 2014 compatibility mode) we had an ‘[SqlException] Execution Timeout Expired’ for a specific query,
which is running fast on test with same amount of data.
We tried different things, at the end the ‘READ UNCOMMITTED’ helped in that case.
I know that it makes no sense to use read uncommitted together with RCSI, but that was the solution.
Why that can happen? There should be no blocking with RCSI?
For personalized support on production databases, feel free to click Consulting at the top of the site and I’d love to help.