Locking, Blocking, and Isolation Levels Archives

Performance Tuning

“But NOLOCK won’t hurt me if I’m using an index!”

September 15, 2026

Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.

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.

Performance Tuning

And Then There Was The Time RCSI Actually Made Query Results More Accurate.

June 10, 2026

Normally when I tell people about SQL Server's optimistic concurrency isolation levels, Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI), I have to give them a little speech about how they need to test their queries because the results can change. Recently, though, I was working with a client who was getting the wrong query…

Performance Tuning

Don’t Let Your Kids Drop ACID.

May 7, 2025

We're heading towards graduation time.

In just a few short months, your high school kids are going to be packing up their bags and heading off to college. It's a time of adventure and discovery, and the first time they'll be away from your parenting and guiding influences.

Performance Tuning

Troubleshooting Mysterious Blocking Caused By sp_getapplock

April 24, 2024

I'm kinda weird. I get excited when I'm troubleshooting a SQL Server problem, and I keep hitting walls.

I'll give you an example. A client came to me because they were struggling with sporadic performance problems in Azure SQL DB, and nothing seemed to make sense:

Performance Tuning

What Do the LCK_M_SCH_M and LCK_M_IS Wait Types Indicate?

April 13, 2022

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.

Performance Tuning

Meme Week: NOLOCK? NOSQL.

February 24, 2022

When you find yourself using NOLOCK, consider NOSQL instead.

NOLOCK has 4 big problems:

Performance Tuning

Meme Week: SET DEADLOCK_PRIORITY HIGH

February 23, 2022

Hey, this seems harmless.

Seriously, don't SET DEADLOCK_PRIORITY HIGH.

Performance Tuning

Error Handling Quiz Week: Will a Transaction Help?

January 11, 2022

In yesterday's post, we tried a TRY/CATCH, and those of us who actually ran the demo scripts were disappointed. We found that a TRY/CATCH alone doesn't fix it: we end up getting rows in the Parent table, but no rows in the Child table. It turns out there are some gotchas in Books Online's summary of…

Performance Tuning

NOLOCK Is Bad And You Probably Shouldn’t Use It.

November 23, 2021

Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.

When you put NOLOCK in your query, SQL Server will:

Performance Tuning

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

January 27, 2021

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 in their special situation.

Performance Tuning

Query Hints You Can Use to Avoid Blocking

January 21, 2021

No, not NOLOCK. You get the wrong query results, even when your query results aren't supposed to be changing.

I'm talking about when you need to do writes, but you want your query to get along with others in high concurrency environments, without creating a blocking firestorm.

Performance Tuning

How to Set & Get the Next ID Without Serializable Isolation

November 25, 2020

Say for some reason you can't use identity columns or sequences, or maybe your application was built a long, long time ago - and you've got a table with a list of IDs to use next:

[crayon-6ae28c9bdf1e3651498454/]

Performance Tuning

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

November 19, 2020

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.

Performance Tuning

DBA Training Plan 18: Managing Concurrency

September 4, 2019

Locking isn't a problem - queries are gonna lock data. As long as nobody else wants the lock, you can take your sweet time locking it, and it won't show up as a problem. (That's one of the many reasons queries work so well in development, and then hit performance issues in production.) Blocking, now…

Performance Tuning

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

August 5, 2019

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…

Performance Tuning

Using Implicit Transactions? You Really Need RCSI.

July 9, 2019

Implicit transactions are a hell of a bad idea in SQL Server: they require you to micromanage your transactions, staying on top of every single thing in code. If you miss just one little DELETE/UPDATE/INSERT operation and don't commit it quickly enough, you can have a blocking firestorm.

The ideal answer is to stop using implicit transactions, and only ask for transactions when you truly need 'em. (Odds are, you don't really need 'em.)

Performance Tuning

Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly

November 14, 2018

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.

Performance Tuning

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

October 16, 2018

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: [crayon-6ae28c9be19a3686911697/] We're just setting everyone's web page to ours.…

Performance Tuning

Locks Taken During Indexed View Modifications

September 5, 2018

Frankenblog
This post has been nagging at me for a while, because I had seen it hinted about in several other places, but never written about beyond passing comments.

A long while back, Conor Cunningham wrote:
This same condition applies to indexed view maintenance, but I’ll save that for another day :).
AFAIK he hasn't written about it or typed an emoji since then.

Performance Tuning

How to Delete Just Some Rows from a Really Big Table: Fast Ordered Deletes

April 27, 2018

Say you've got a table with millions or billions of rows, and you need to delete some rows. Deleting ALL of them is fast and easy - just do TRUNCATE TABLE - but things get much harder when you need to delete a small percentage of them, say 5%.

It's especially painful if you need to do regular archiving jobs, like deleting the oldest 30 days of data from a table with 10 years of data in it.