Introduction

Have you ever encountered blocking problems in your SQL Server databases due to lock escalation? As a DBA, I certainly have! Lock escalation can cause queries to grind to a halt as they wait for locks, slowing down the entire system. It’s a frustrating issue, but luckily there are ways to address it. In this article, we’ll take an in-depth look at lock escalation – what causes it, how it can lead to blocking, and most importantly, whether changing the lock escalation behavior is a good solution. So grab a coffee and let’s dive in!

What is Lock Escalation?

First, let’s make sure we’re on the same page about what lock escalation actually is. In SQL Server, locks are acquired on database objects like rows, pages, and tables to maintain data integrity during modifications. Normally, locks are acquired at the lowest granular level needed, like row locks for single row updates.

However, if a query needs to acquire a large number of row or page locks, SQL Server may decide to “escalate” those many fine-grained locks into a single table lock instead. This is more efficient from a system resource perspective. The threshold that triggers lock escalation is controlled by the locks table option.

So in a nutshell, lock escalation is when SQL Server trades many fine-grained locks for fewer coarse-grained locks to reduce overhead. Makes sense, right? But this behavior can sometimes cause issues…

How Lock Escalation Leads to Blocking

The problem with lock escalation is that when a query acquires that exclusive table lock, it blocks all other queries from accessing the table until it’s done, even if they only need to read a single row. I’m sure you can see how this could bring concurrency to a screeching halt!

Here’s a simple example:

If Query 1 updates enough rows to trigger lock escalation, it will acquire an exclusive lock on the whole Products table. Poor Query 2 will be blocked until Query 1 finishes, even though it only needs to read a single row that Query 1 isn’t even touching! No bueno.

Should You Change the Lock Escalation Behavior?

So if lock escalation is causing blocking issues, should you change SQL Server’s default behavior? Well, there are two main options:

1. Disable lock escalation entirely for the table using ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE)
2. Increase the lock escalation threshold using ALTER TABLE ... SET (LOCK_ESCALATION_THRESHOLD = ##)

Disabling lock escalation may seem tempting, but I’d advise caution. It’ll allow more concurrency, but at the cost of increased memory utilization for locks. If you’re not careful, you could trade blocking issues for memory pressure and performance problems.

Increasing the threshold is a bit safer, as it still allows escalation at a higher threshold. But again, test the impact on your workload and system resources.

In my experience, disabling lock escalation entirely is rarely the right call. It’s better to dig into why so many rows are being locked in the first place. Can the queries be optimized to touch less data, perhaps with an index? Can the transaction be committed sooner?

Conclusion

Lock escalation is a necessary feature of SQL Server that can sometimes get in the way of concurrency. While disabling it may be a quick fix, it’s often just hiding deeper issues. I recommend examining the root cause of excessive row locks first, and only if absolutely needed, increase the escalation threshold cautiously. With careful analysis and tuning, you can achieve a good balance between concurrency and resource utilization.

I hope this lock escalation overview has been helpful! Let me know in the comments if you have any other questions. Happy querying!

Microsoft documentation link: Lock escalation