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 that’s where the problem shows up. Blocking means that someone is holding a lock, and they’re blocking someone else from getting the data they need. Even worse is “deadlock” – that term strikes fear in the hearts of database administrators. Deadlock issues strike without warning, kill transactions dead, and slip away without a trace. Because they’re so transient, they’re hard to troubleshoot.

Good news, though – this stuff really isn’t hard to track down once you use a methodology. Here’s mine: first, identify if blocking is slowing things down.

1. Run sp_BlitzFirst @SinceStartup = 1 to see if LCK% waits are a significant problem for you, or if your server is bored. Note the number of lock waits – if you’ve only had a few dozen over the course of a day, then it might have just been a few queries held up when someone was trying to create or rebuild an index. If you see hundreds (or heaven forbid, millions or billions) of these waits per day, especially with long (seconds or more) average wait times, then we’ve probably got a problem.
2. Run sp_WhoIsActive and see if LCK% waits are showing up for queries running right now. This will help you identify which queries are involved, and what databases they’re running in.
3. Finally, run sp_BlitzIndex @GetAllDatabases = 1 and look for “Aggressive Indexes” warnings pointing to the tables that are involved in the blocking. These indexes/tables aren’t necessarily the problem – they’re just involved with the blocking, just like you are. We wouldn’t fire you just because you’re involved with the blocking. (Well, maybe we would.) Interpreting these Aggressive Indexes warnings can be a little tricky (just like interpreting those Human Resources warning emails you keep getting.)

Once you’ve identified how big the problem is, which queries are involved, and what tables & indexes they’re trying to lock onto, then we can start fixing the root cause of the problem. There’s three ways to do it, from easiest to most work required:

1. Tune your indexes – get rid of the indexes you don’t need, and add the right indexes to help queries jump in and out quickly without holding huge locks to do table scans.
2. Tune your queries – get rid of implicit conversions, functions in the WHERE clause, and other SARGability foes that cause your tables to scan an entire index rather than doing a seek. The less data we can read & lock, the more predictable and small our locks become.
3. Change your isolation level – let queries cooperate with RCSI or SNAPSHOT isolation instead of waiting in line for locks. That last one is the easiest in theory, but just make sure you read that entire post (and the linked ones in it) to understand what you’re getting into, since it can backfire.