In this demo I show a bit of the free sp_WhoIsActive procedure by Adam Machanic. You can download it from whoisactive.com.

Transcript

There are more secrets of NOLOCK.

The second secret of NOLOCK– and this is important to know– is that it isn’t really what it says

The word says “NO” LOCK. It’s not actually NOLOCK!

NOLOCK is less-lock, it does use some locks, and we can easily prove this.

What I’m going to do, in my other session, I’m going to copy this code, and I’m going to paste it in. We’re going to get rid of our updates there. Our app has healed itself, it’s no longer haywire. What we’re going to do is: we’re going to start what looks like it would be a fully online operation. I’m going to open a transaction, which I don’t normally need to do with an ALTER INDEX REBUILD command. I’m doing this simply so that I can leave the transaction open and show the effect of the locks without having to run everything at the exact same time.

I’m beginning my transaction and I’m altering my non-clustered index that I just created on that small table. I’m rebuilding it online. I’m using an expensive Enterprise feature to rebuild my index online, but I have not committed my transaction.

While that ALTER INDEX REBUILD is running, if I try to select from that table, even with NOLOCK– And this would seem like it must work– my INDEX REBUILD is online, and I’m using NOLOCK. How could I be blocked? How could this happen? Well, wow, that query’s taking a long time to run. It took 6 seconds, 7 seconds.

If I look in Activity Monitor, it doesn’t tell me everything…

But it does tell me, okay, we’ve got some lock waits again. We’ve certainly got some lock waits. If I go ahead and look up at what’s happening, here on this command, which helpfully says execute, I have a LCK_M_SCH_S lock. I have a schema stability lock wait. I can get a little bit of information about this. I can look at the details of it in Activity Monitor, and I can see, okay, this is the one who is blocked: it’s my select top 1 query.

I even have information that it is blocked by– if I expand this heading– it is blocked by session 63. And I can go down to session 63 and say details.

Okay, you’re blocked by an index rebuild, but if I want to know more about the lock that it’s waiting on– you said NOLOCK, why are you waiting on a lock? I don’t get a lot of extra information here.

There is a tool, a free tool, called sp_WhoIsActive

It’s written by a fellow named Adam Machanic. It is a great way to say: I want to get a lot of information about the activity running on my SQL Server, and I want a lot of details.

I’m going to used an option called @get_Locks=1, and I’m going to close out Activity Monitor, so that it’s not still polling my SQL Server. I’m going to open another window, and I’m going to run sp_WhoIsActive. Looking at this, I can see the 2 queries we’re running. I don’t have all those other processes showing, who aren’t really doing anything right now. It just by default is showing me, okay, here is who’s doing something, or who has an open transaction. I love that it hones in on that stuff! It also gives me that wait info right up front, and the blocking_session_id as well.

This session is blocked by session 63. If I go back here, sure enough, this is session 63. I can also click on these commands and see them too. So, I like this better. I see this as a smarter, more intuitive, Activity Monitor.

And what I want to know, I used @get_locks=1. And against a busy system, @get_locks has to look at all the issued locks, and all the waiting locks, so it can be slow if you’ve got a lot of activity on your SQL Server. So, know that if you use this parameter, it can slow down sp_WhoIsActive, just because the dynamic management view it pulls from can have a lot of info and be slow to return.

But, what I want to know is, for my query who has NOLOCK: OK, I said NOLOCK, what lock are you waiting on? And what lock is being held by this online index rebiuld?

This is a little graph of all of the locks my NOLOCKED command wanted

Against the database, it needs a shared lock against the database itself. That makes sense. I mean, even if I’m using NOLOCK, what happens if someone drops the database while I’m running my query?

And then also against ref.FirstName. We have a schema stability lock that we are waiting on. For ref.FirstName, someone else has a schema modification lock against it: that INDEX REBUILD. Even if it’s online– when it’s online, it builds a nice lovely new index structure. But it has to do a metadata change at the end of the operation where it switches in the new structure. Switching that in, requires a modification lock on the object, saying, hey, I’m changing something around, you really shouldn’t use this table while I’m doing it.

We can’t get a schema stability lock for our NOLOCK query while that’s happening, because even a NOLOCK query has to make sure that the table isn’t pulled out from underneath it while it’s running.

NOLOCK is really less locks needed

It can still be blocked, and it can still be part of a blocking chain.

While I’m waiting on this, I may block others as well.

In defense, I will briefly say, in defense of the ALTER INDEX REBUILD, we do have some new options in terms of this. And it is definitely a bad practice to run your ALTER INDEX REBUILD and leave it in an open transaction like this. This is my fault!

In recent versions of SQL Server, there are new parameters we have about {what to do} if this gets blocked. If our ALTER INDEX REBUILD gets blocked when it’s trying to do something, like do that really quick metadata change at the end, that requires that schema modification lock. Should it wait at a low priority so that it doesn’t cause a big blocking chain? Things like that.

So, I don’t mean to show this as a prototype. This is NOT how you should run ALTER INDEX REBUILD, but it just is an easy way to show the locks that a NOLOCK query will require.

In summary: NOLOCK absolutely does need some locks

Now that I rolled back, or canceled that online INDEX REBUILD, my query became unblocked and was able to return. For some reason, in the year 1974, the name Condredge was popular, and has never been used since in the United States, at least not with 5 babies a year. That’s our minimum, we only get reported names when there’s at least 5 babies a year with that name and this dataset.