Locking, Blocking, and Isolation Levels

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

Partition Level Locking: Explanations From Outer Space

It’s not that I don’t like partitioning It’s just that most of my time talking about it is convincing people not to use it. They always wanna use it for the wrong reasons, and I can sort of understand why. Microsoft says you can partition for performance. Partitioning large tables or indexes can have the…
Read More

Locking When There’s Nothing To Lock

Demo Day We use StackOverflow for demos a lot. For all the reasons Brent mentions in his Great Post, Brent©, it’s pretty awesome. Where things get tricky is with locking demos. Sometimes the modifications can take a long time. This may be by design if you need to show long-held locks by sessions that aren’t…
Read More

Does Creating an Indexed View Require Exclusive Locks on an Underlying Table?

An interesting question came up in our SQL Server Performance Tuning course in Chicago: when creating an indexed view, does it require an exclusive lock on the underlying table or tables? Let’s test it out with a simple indexed view run against a non-production environment. (AKA, a VM on my laptop running SQL Server 2014.)…
Read More