Why I Love NOLOCK Hints In SQL Server (Video Edition)

Oh, Just Do it.

---

Video Summary

In this video, I delve into why NOLOCK is my favorite query hint in SQL Server, presenting it as a tongue-in-cheek exploration of its prevalence and implications. I discuss how seeing NOLOCK hints everywhere often indicates problematic code that requires significant tuning and training to fix, leading to increased consulting opportunities. The video also touches on the benefits of enabling read-committed snapshot isolation and accelerated database recovery, which allow for cleaner queries without NOLOCK hints, ultimately making applications more robust and bug-free.

Full Transcript

Erik Darling here with Darling Data, recording this video with Camtasia because I tried recording it with Streamlabs again and shockingly did not go well. I listened to the whole video and about three quarters of the audio managed to drop out. So we’re going to stop doing that unless I can find some really incredible way of fixing that issue. And in this video, where the intent is a rather tongue-in-cheek video, is to talk about why NOLOCK is my favorite query hint in all of SQL Server. Now, the first reason is because I am a consultant. In other words, I get paid to fix problems. And NOLOCK is a big problem. Generally, the more NOLOCK hints that I see when I look at code. I can abide a few of them. But generally, the more NOLOCK hints that I see when I look at code, the more money I know that I’m going to make. I know that I’m going to make more money because the developers are going to need a lot more training. No one who knows what they’re doing puts NOLOCK hints everywhere. The code is going to need a lot of tuning because no code that runs quickly needs NOLOCK hints everywhere.

The indexes are going to need a lot of fixing because especially for modification queries, if they were finishing quickly, if they had good indexes to find the data that they were after, and they don’t need to modify a bajillion indexes in order to complete their work, well, they’d run pretty quickly too. Now, granted, there are certainly exceptions, like if you’re using the help hit known as merge, or if you’re for some reason trying to modify a kajillion rows at once, that could certainly hamper things a bit, but in general, most normal workloads, merge is an abnormal part of a workload, need my help for that. And I think probably the thing that gets me the most excited though is that I know when I see NOLOCK hints everywhere that there are going to be serious bugs in the software because you’re going to be reading dirty, crappy data from dirty, crappy tables.

Okay, the tables aren’t dirty and crappy, but the reads are. And when that happens, you’re going to read bad data and that bad data is going to end up in results and possibly used to do other things with NOLOCK hints that are also based on bad data. And there’s just going to be a lot of bad data floating around. And the more bad data floating around there is, the more chance of there being bugs in results and logic and all this, all the other good stuff that makes an application run well. I think the only thing that makes me also see cash registers floating in the sky is, wear an entity framework only shop. Because when I hear that, oh boy, start looking at private jets.

Another reason that I love NOLOCK is because no one actually knows what it does. Even though when I point it out and I say, hey, developers, how come there’s NOLOCK hints everywhere? Someone will stand up with all the confidence in the world, all the confidence in the known universe, the Padasha Emperor of Confidence. And they will say, so my query doesn’t take any locks. And I stare at the camera for a while and I say, aha, well, bad news. It’s not quite it.

You know, NOLOCK queries will still take that schema stability lock out. But really what the NOLOCK hint does is the same thing as the read uncommitted isolation level. And that is read bad data that is in the middle of being changed. Now, you can see the same rows with the same values twice, depending on what columns you’re selecting.

You could likewise see the same rows with different values twice, depending on what columns and rows you’re selecting. And you could also miss rows entirely with it. Now, granted, I do grant you this, that you can run into a lot of that with the read-committed pessimistic isolation level, which I find really not far off in its despicableness from the read uncommitted or NOLOCK isolation level.

But, yeah, it really does make things crappy. And, you know, in highly transactional systems, that can really cause all sorts of bugs, problems, issues. And best of all, one of my absolute favorite things in the world to do is turn on read-committed snapshot isolation.

And the reason I love doing that is because I get to turn it on. And in 2019, I also get to turn on accelerated database recovery. So I use the local version store rather than 10DB, which is phenomenal.

Love accelerated database recovery. Probably one of the best features that’s landed in SQL Server in I don’t know how long. But the best part is I get to turn that on and hang around and I get to delete all those NOLOCK hints and all those set transaction isolation level read uncommitted and I get paid to do it.

And I could keep doing that forever and ever while I look at private jets. And probably the third and final reason why I love NOLOCK hint is because I get to keep talking about it. I get to keep talking about it and writing about it and producing blog content about it and video content about it.

Along with, like, well, I mean, there probably used to be hundreds of other bloggers. These days, they all work at Microsoft and don’t do anything with themselves. Aside from work at Microsoft, it might be down to, like, a dozen or maybe two dozen bloggers that can still write and record videos and make content about how crappy NOLOCK is.

And a lot of them are consultants. And most of the time, no one listens to you until they’re paying you. So as a consultant who makes a lot of blog and video content and who gets paid to fix these things, I love the NOLOCK hint.

It is the gift that keeps on giving. It would be like if you opened up a bar slash bail bond shop across the street from a courthouse, people would just walk by and golf clap at your prowess in securing a prime location for your two businesses.

That is what NOLOCK, having NOLOCK and SQL Server is like. It is phenomenal. It is amazing.

I want to thank Microsoft. I want to thank whatever, like, original database standards committee said we need a way to give people bad data for some reason. I don’t know why.

But, yeah, it is miraculous that no matter how much blood and ink is spilled and dedicated to telling people and teaching people about why NOLOCK in, like, 95% of cases is a bad idea and not what you want to use and not what you want to be doing, people still will tell you that it’s a best practice and, like, their company’s coding guidelines or, like, you’re not allowed to have a query without it. But I’ve worked with some clients where with NOLOCK was a keyboard shortcut, pasting it in.

It’s truly a thing. It’s truly a mystery. Like, you’ll see it in, like, modification queries.

It is truly astounding and miraculous. And, again, I could fix that all day long because it’s really easy for me to fix that. And you won’t need NOLOCK hints anymore.

And you’ll wonder what you ever did without me. Just kidding. I don’t think. I’m not sure my wife even does that. She might wonder how she ever thought that she knew what true annoyance was without me.

Anyway, thank you for watching my inaugural video with Camtasia. I hope you learned something. I hope you enjoyed yourselves.

I hope the tongue-in-cheekness of this video came through. If you like the video, please give it a thumbs up. If you like amusing, fun SQL Server content, subscribe to my channel so that you get all the bling, bling, bling notifications whenever I drop one of these amazing videos. And, yeah, I think that’ll just about do it.

Thank you again for watching. And I will see you in another video real soon. Depending on how long it takes me to render and upload this video and make sure that all the audio and video is okay in it.

Well, it could be sooner than later. We’ll have to wait and see, won’t we? Anyway, thank you for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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.