Locking, Blocking, and Isolation Levels

What Do the LCK_M_SCH_M and LCK_M_IS Wait Types Indicate?

You’re investigating your SQL Server’s top wait stats, and you’re noticing a lot of LCK% waits. Normally, that indicates blocking and deadlocks, but you’re just not getting complaints from your end users, and you’re wondering what’s causing it. It might be overzealous index rebuilds. Let’s demo why by starting a new query in the Stack…
Read More

“But Surely NOLOCK Is Okay If No One’s Changing Data, Right?”

Some of y’all, bless your hearts, are really, really, really in love with NOLOCK. I’ve shown you how you get incorrect results when someone’s updating the rows, and I’ve shown how you get wrong-o results when someone’s updating unrelated rows. It doesn’t matter – there’s always one of you out there who believes NOLOCK is okay…
Read More

When You’re Troubleshooting Blocking, Look at Query #2, Too.

When I’m troubleshooting a blocking emergency, the culprit is usually the query at the head of a blocking chain. Somebody did something ill-advised like starting a transaction and then locking a whole bunch of tables. But sometimes, the lead blocker isn’t the real problem. It’s query #2. Here’s a sample scenario: A long-running select with…
Read More

“But NOLOCK Is Okay When My Data Isn’t Changing, Right?”

I’ve already covered how NOLOCK gives you random results when you’re querying data that’s changing, and that’s a really powerful demo to show folks who think NOLOCK is safe to use in production. However, I’ve gotten a question from several users: But I’m querying data that isn’t changing – sure, OTHER rows in the table…
Read More

Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly

Legendreary In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up. I didn’t even get to the point where I wanted to test all those relationships. But there’s an even worse surprise waiting for you if…
Read More

Using NOLOCK? Here’s How You’ll Get the Wrong Query Results.

Slapping WITH (NOLOCK) on your query seems to make it go faster – but what’s the drawback? Let’s take a look. We’ll start with the free StackOverflow.com public database – any one of them will do, even the 10GB mini one – and run this query: Transact-SQL UPDATE dbo.Users SET WebsiteUrl = 'https://www.BrentOzar.com/'; 12 UPDATE…
Read More