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 nolock starts, taking out a schema stability lock: nobody can change the structure of the table while he runs An online index operation needs to finish up, which needs a schema modification lock on that same table in order to switch in the newly built index