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