Locking and Blocking in SQL Server

Microsoft SQL Server uses “pessimistic” locking by default– your queries are naturally defensive, paranoid that they might read the wrong data. It’s been the same way for decades, and it’s one of the reasons that apps can seem slow on SQL Server.

There are 3 easy fixes:

1. Have just the right amount of indexes – enough to make your queries go fast, but not so many that inserts/updates/deletes slow down. SQL Server has to keep all your indexes in sync in real time, which means the more indexes you have, the longer your locks are held, and the more likely that you’ll run into blocking & deadlocking issues. Learn how to do this in my Fundamentals of Index Tuning class, then moving on to my Mastering Index Tuning class.
2. Tune your deadlock-involved queries to be as short & sweet as possible, work through tables in a predictable order. I cover how to do that in my Fundamentals of Query Tuning class, then moving on to my Mastering Query Tuning class.
3. Use the right isolation level for your app’s needs – the default (read committed, aka pessimistic) is probably the wrong one for you. Read uncommitted, aka nolock, is usually a bad idea too. Instead, you either want read committed snapshot isolation (RCSI) or snapshot isolation. This one requires server-level tuning first to make sure your server can handle the additional workload, and I cover that in my Fundamentals of TempDB class, then moving on to my Mastering Server Tuning class.

You don’t have to do all 3!

Just pick the one you’re the most comfortable with – tuning indexes, tuning queries, or making server-level changes – and do that one first. In most cases, you can do just one of them, and the blocking & deadlocking will gradually start dropping away.

How to Tell if Locks Are a Problem For You

Before you spend time focusing on blocking & deadlocking, let’s find out if it’s actually the biggest problem facing your SQL Server. Start by downloading our free open source SQL Server First Responder Kit and running the Install-All-Scripts.sql in your production SQL Server’s master database. (In Azure SQL DB, put it in your user database instead.)

You have to run these in production because locking data is stored only in memory, and it disappears whenever SQL Server restarts. If you run it in development, you won’t see the same locking issues that are happening under real production workloads.

Then, let’s check to see if your queries have been waiting on locks. Run this:

This will list the top waits, things your queries have waited on. If you see waits that start with LCK, that means your queries are waiting on locking. I teach you more about interpreting those numbers in the Mastering Server Tuning class, but suffice it to say that if LCK waits are in your top 4-5 waits, you’ve got a blocking problem.

Next, to find the tables involved with the waits, run:

That’s an index design analysis tool. In the output, look for blocking warnings. That’ll tell you which tables & indexes are involved in the blocking – but that doesn’t mean the indexes are the problem. That’s just the list of indexes where the blocking is happening. Dropping the index won’t solve the problem! You’ll just see the blocking manifest on other indexes instead.

Finally, to check if you’re having deadlocking, run:

That analyzes the recent deadlocks that are currently stored in memory. The top result set is an itemized list of recent deadlocks, and the second result set is an analytical summary. It’ll show which queries and tables have been involved in the most deadlocks recently.

If blocking shows up in any of these 3 scripts, then it’s time to start working through the solutions at the top of the page. Don’t be afraid! It really isn’t hard, and armed with those 3 solutions, you can knock out blocking & deadlocking problems pretty quickly. They’re actually amongst the easiest of the problems I solve!

Or, You Can Hire Me to Fix It.

Seriously – I’d rather you hit up my training classes to learn this stuff so that you can fix it yourself going forward. These problems aren’t going away, and you’re going to face ’em with every app you ever build. Attending my classes is the cheapest, most cost-effective way for you to knock those problems out.

But if you haven’t got time for the pain, my 2-day SQL Critical Care® consulting engagement will identify exactly where the blocking is coming from and tell you exactly what queries & indexes to fix, and how! In just 2 days, you’ll have the answers you need to make the pains go away.