Lock Escalations are really, really bad, because you end up with an Exclusive or Shared Lock at the table level. And placing such restrictive locks at the table level reduces your concurrency and the throughput of your database.

I don’t want to talk today about the basics of Lock Escalations. Today I want to concentrate more on how the shape of the Execution Plan influences the locking behaviour of SQL Server and when Lock Escalations are triggered.

Filter Operators – your enemies!

Let’s assume the following very simple query:

SELECT * FROM Sales.SalesOrderDetail
WHERE ModifiedDate > '20200501'
GO

As you can see, I’m just requesting rows from the table Sales.SalesOrderDetail where the ModifiedDate is older than May 1, 2020. Of course this query doesn’t return any records, because the chosen date is in the future.

But when you look at the Execution Plan, you can see that the Query Optimizer has chosen a complete Clustered Index Scan operator followed by an explicit Filter operator.

A complete Index Scan followed by a explicit Filter operator is a really bad pattern in an Execution Plan. It means that at the start (at the Scan operator) you are reading a huge amount of data, and later (at the Filter operator) you are eliminating non-qualifying records. You have read more rows physically than you have requested logically. In my case I have read 121317 rows from the table Sales.SalesOrderDetail, but no row has satisfied my search predicate (based on the column ModifiedDate). Therefore all rows were eliminated at the Filter operator.

Imagine now that you run this query in a more restrictive Transaction Isolation Level like Repeatable Read. In that case SQL Server has to hold the Shared Locks until the end of the transaction. In that case, SQL Server will trigger a Lock Escalation during the Clustered Index Scan, when you have read more than 5000 rows. That’s bad, because afterwards the read non-qualifying rows are eliminated at the Filter operator. You have triggered a Lock Escalation for nothing…

Residual Predicates – your friends!

You have to accept that Filter operators are really terrible. But can we do better? Yes, because SQL Server also supports so-called Residual Predicates: a predicate that is *directly* evaluated inside the Storage Engine when a record is read off the data page. When the predicate qualifies, the row is passed outside the Storage Engine into the Execution Plan. If the predicate doesn’t qualify, the row is just discarded and disappears. It will not be handed off to the next operator in the Execution Plan.

This is very different from an explicit Filter operator! With a Residual Predicate you are only processing those rows in the Execution Plan that you have logically requested (but you still have to read the requested data page…). When no row qualifies based on the Residual Predicate, no rows are „transported“ through the Execution Plan. Let’s have a look at the following query.

SELECT * FROM Person.Person WITH (INDEX(1))
WHERE ModifiedDate > '20200501'
GO

Here I am requesting rows from the table Person.Person, and again no row will qualify for the chosen Search Predicate. But this time SQL Server is able to push down the Search Predicate into the Storage Engine in the form of a Residual Predicate. Therefore the Search Predicate is directly evaluated within the Storage Engine:

When you now run this simple SELECT statement in the Transaction Isolation Level Repeatable Read, you will no longer trigger a Lock Escalation, because within the Execution Plan you don’t process any rows – they were already discarded within the Storage Engine. So whether or not you trigger a Lock Escalation really depends on the shape of the Execution Plan for a given query…

Summary

As you have seen in this blog posting, the shape of the Execution Plan has a huge influence on the locking behavior of SQL Server. The Residual Predicate is just like a Filter operator, but is directly evaluated within the Storage Engine after reading a row off the data page. It’s a performance optimization employed by SQL Server.

In some cases the Query Optimizer is able to push down a Search Predicate as a Residual Predicate into the Storage Engine, but in some cases it is not possible and the Query Optimizer introduces an explicit Filter operator. Don’t ask me in which cases it is possible and when it is not. This behavior is not really documented by Microsoft…

Thanks for your time,

-Klaus