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/
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.
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.
Ben, 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.
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.