Why NOLOCK Is My Favorite SQL Server Query Hint

Because I’m A Consultant

---

And generally, the more NOLOCK hints I see, the more money I know I’m going to make.

It shows me four things right off the bat:

• The developers need a lot of training
• The code needs a lot of tuning
• The indexes need a lot of adjusting
• There are probably some serious bugs in the software

Perhaps the only other thing that signals just how badly someone needs a lot of help is hearing “we’re an Entity Framework only shop”.

Cha-ching.

Because No One Knows What It Does

---

With the utmost confidence, I’ve heard hundreds of developers say “it keeps my query from taking locks”.

Would that I could take that confidence and share a shred of it with everyone in the world, we could conquer space in a fortnight.

So I get to do fun things like explain to them how it still takes some locks, but mostly how it ignores locks taken by other queries, and that’s why it can:

• See the same row twice with the same values
• See the same row twice with different values
• Miss rows entirely

And in highly transactional systems, that can cause real problems. Even in systems where the load isn’t anything insane, it can cause all sorts of bugs and race conditions.

And best of all, because I get to hang around deleting those hints after switching over to Read Committed Snapshot Isolation.

Because I Get To Keep Writing About It

---

And no matter how much I do, no one listens. At all. Ever.

Week in and week out, I work with brand new people who have no idea that their results are full of incorrect, inconsistent data, and general bugginess.

I could schedule this same post every week for the next five years, and I’d still see the same amount of people with the same problems, over and over again.

Seriously. I love it. It’s the gift that keeps on giving.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.