Performance Tuning
“But NOLOCK won’t hurt me if I’m using an index!”
NOLOCK is bad and you probably shouldn’t use it, but every time I mention that publicly, the pushback just keeps coming. I don’t know why people so firmly believe that their situation couldn’t possibly be affected by bad/random data from NOLOCK.
Today’s misconception comes from a LinkedIn commenter telling me it’s safe to use if you’re doing index seeks. Hoo boy. Let’s whip up a table with an index:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DROP TABLE IF EXISTS dbo.NolockTest; CREATE TABLE dbo.NolockTest (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Label VARCHAR(20), Score INT); CREATE INDEX Label ON dbo.NolockTest(Label); INSERT INTO dbo.NolockTest(Label, Score) SELECT CASE WHEN value % 2 = 0 THEN 'Even' ELSE 'Odd' END, value % 2 FROM generate_series(1,1000000); SELECT TOP 100 * FROM dbo.NolockTest; |
The contents of the table are pretty straightforward: a bunch of rows with Odd & 1, Even & 0:
There are no rows that say Odd & 0, and no rows that say Even & 1. You can revisit the table population script above if you’re not sure.
Let’s start a transaction that flips the Even rows over to Odd:
|
1 2 3 4 |
BEGIN TRAN UPDATE dbo.NolockTest SET Label = 'Odd', Score = 1 WHERE Label = 'Even'; |
While that query is actively running, changing rows, run this in another window. I’m purposely using an index hint here to make the demo easier for y’all to reproduce without worrying about how many rows will come back, what your compat level is set to, etc. I’m just guaranteeing it’ll get an index seek because the SQL Server and Azure SME said those were invulnerable:
|
1 2 3 4 |
SELECT Label, Score FROM dbo.NolockTest WITH (INDEX = Label, NOLOCK) WHERE Label = 'Even' AND Score <> 0 ORDER BY Label; |
That query SHOULDN’T return any rows. It should be impossible to see any rows where the label says Even, and the score says 1, and yet, there are thousands:
The reason NOLOCK gets bogus results here is due to the index seek itself, and the way it works. Here’s the query plan:
SQL Server works through this query plan from right to left, in order. The first thing it does is seek on the index on Label, making a list of “Even” rows that match. The second thing it does for each of those rows is the key lookup, because our query wants more columns than are present in the index. Under most isolation levels, the results are still accurate because SQL Server honors the locks being held by in-flight transactions.
With NOLOCK, not so much: SQL Server ignores the locks on in-flight transactions, reading rows as they’re being modified. So when it opens the index, it may find rows that say “Even” – but that have already been changed on the clustered index to be “Odd”, with a Score = 1! At any given time, you can have rows where the values on an index can be DIFFERENT from the values on the clustered index, and that’s fine, and that’s by design.
Shout out to Kendra Little, who first showed me a seek-plus-key-lookup demo like that years ago and I nearly fell off my chair with happiness at the elegance of that demo.
Right about now, someone out there is shaking their fist at the screen, yelling, “You could fix this problem by making the index covering!” In the real world, we can’t just make every index a covering index for every other query.
I say this same thing every time NOLOCK comes up: if you’re okay with incorrect query results, NOLOCK is completely fine. It sounds like I’m being sarcastic, but yes, there are scenarios out there where accuracy in the result set doesn’t really matter. Data warehouse reports for executives are the typical use case – after all, let’s be honest, your executives are going to make their own bad decisions no matter what the data says. (Some of you are laughing, and you shouldn’t be, because some of you have slathered NOLOCK all over your data warehouse queries.)
Otherwise, if you need any kind of accuracy in your data, NOLOCK is bad and you probably shouldn’t use it. Read the contents of that blog post again.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
What I really want is the best way to just skip over records that are currently locked by another process for writing. What hint or option should I use?
The isolation levels Read Committed Snapshot Isolation or Snapshot Isolation. Learn more here: https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/
There is also the READPAST hint, but nowadays RCSI is preferred
Respectfully, sir, sp_Blitz, sp_BlitzFirst, and sp_BlitzIndex all have NOLOCK hints…
Yes, because that’s not transactional data that requires accuracy. We’d rather sacrifice accuracy for speed, and for not blocking system objects. The system objects don’t have an RCSI or SI option – if they did, I’d gladly use ’em!
I find it difficult to believe that people are still using NOLOCK. SQL Server 2005 introduced multi-version concurrency features that work so much better than READ UNCOMMITTED or NOLOCK.
Go to your reporting team and ask them to do a quick search for nolock in their source control or reporting system. You’d be shocked at how often it turns up as part of standard practice in reporting systems.
Most of the people that use it don’t even care about concurrency; they care about their ego. They think it is a special sauce turbo button for making queries going faster they pat themselves on the back over. At one point it was, before SQL Server was able to skip taking out full locks it didn’t need to take out, but that optimization came out in SQL 7.
So last week you mentioned that everything I said was wrong but your post only tries to
disprove the last thing I said (it works for index seeks too). It did seem pretty obvious
that your way to attempt to disprove would be to go down the key lookup route, even though
I made no mention of key lookup in my comment.
I only made reference to my fixes applying to the link in your post which wasn’t even your website. However, the fixes did appear to fix all 3 of your previous posts including the magical index re-org.
I’m just wondering how long this comment will stay on here before you remove and block me
in the same way you did on LinkedIn because I made reference to your bullying tactics.
It’s not a problem, I’ll screenshot it just in case and I’ll also repost my potential fixes
just so people get to see them rather than only the stuff you want them to see.
Oh and just so everyone can see, my name is Rob Horrocks. Unlike you, whether I’m right or wrong, I’m not embarrassed. Everyone makes mistakes, everyone learns new things, even
someone who thinks he’s now completed SQL Server and is ready to teach Postgres.
Rob, you never answered any of my followup questions, and you resorted to personally attacking me.
That’s not welcome on LinkedIn, and it’s not welcome here.
Best of luck on your journey.
(Edited: an earlier version said “Ben” because I was chatting to a friend of mine named Ben at the time I was writing it, hahaha.)
I happen to work at a bank that not only uses NOLOCK, but pretty much every single query that is out there happens to be littered with it. Isolation levels – what even are those?
When asking questions about data accuracy, I’m persistently told that this simply isn’t an issue – the ledger is append-only and the rest is gravy.
At a financial institution. That handles money.
I feel your pain.
I work in a similar industry and the former DBA-cum-Data Platform director will not entertain snapshot isolation levels. It makes me want to cry.
I try this… get a totally different result 0 records returned. Oh right I turned on RCSI that’s why.
Why isn’t RCSI on by default? Why not opt out rather then opt in.
It is on Azure SQL DB, and to some extent on Availability Group readable secondaries.
I think Microsoft’s reasoning is that they’d rather give us a predictable version of SQL Server than an unpredictable changing one, and I like that approach. I would rather they be conservative too. (But I would also love RCSI on by default in new databases, so it’s a really tough question.)
Good on you Brent, keep it up.
I always liken NOLOCK to going to the toilet and locking the door of course. Then a disrespectful person kicks the door down, runs in, grabs all the Soil Heap In Transit, before dashing off. They never know what they’ll end up with. LOL.
[…] Brent Ozar proves a point: […]
No es correcta esa teoria, si hacemos un pequeño cambio para validarlo sin un aislamiento de motor “begin tran”, se podría rechazar lo estipulado.
si crea la tabla con un índice de impacto,
–CREATE INDEX Label ON dbo.NolockTest(Label);
CREATE NONCLUSTERED INDEX idx_test ON [dbo].[NolockTest] ([Label],[Score])
aumentamos la inserción de datos
de 1000000 a 10000000:
retiramos el begin tran
–BEGIN TRAN
UPDATE dbo.NolockTest
SET Label = ‘Odd’, Score = 1
WHERE Label = ‘Even’;
y ejecutamos
SELECT Label, Score
FROM dbo.NolockTest WITH (INDEX = idx_test, NOLOCK)
WHERE Label = ‘Even’ AND Score <> 0
ORDER BY Label;
cambiando solo el nombre del indice
WITH (INDEX = idx_test, NOLOCK)
apreciamos que la información es retornada y lo mejor es que no afecta.
Hi Luis. So you’re saying, “Just don’t use transactions and you won’t have the problem.”
That’s an interesting approach, and I’ve never heard someone say that.
I’d be curious to hear how that works out for you in the real world, where – believe it or not – people actually use transactions.
Thanks for stopping by!
No, jamás he dicho eso, lo que digo es que la transaccionalidad abierta en su ejemplo jamás traerá la información y no ocurre por el uso del NOLOCK, es por el aislamiento que hace, lo que le digo es que el índice debe tener un concepto de construcción correcta, en este caso de cobertura, el uso del NOLOCK, es precisamente leer los datos que estén bloqueados, por ello yo aumento el tamaño del recorrido de la tabla para que sea un bloqueo exclusivo de la transacción y pruebo la lectura del NOLOCK y la respuesta es correcta y efectiva. en conclusión no es malo usar la sentencia, lo que se debe tener es precaución de como usar la combinación de una aislamiento del motor con un NOLOCK, lo cual muestra claramente que hasta no terminar ya sea un commit o un rollback, la lectura no se completa.
Luis – I address that in the post in this section:
“ Right about now, someone out there is shaking their fist at the screen, yelling, “You could fix this problem by making the index covering!” In the real world, we can’t just make every index a covering index for every other query.”
I salute you for reading blog posts in another language, and replying! That’s awesome. I know just enough Spanish to get myself in and out of trouble, and I have so much respect for people like you that can do technical work across languages. You rock!