Lock Escalations and Execution Plan shapes

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

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:

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 Filter operator - your enemy!

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.

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:

A Residual Predicate - a friend of you...

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

6 Comments

  • Klaus, thanks for the article and in particular for the summary. Before reading the summary, I was going to ask you, how the hell did you manage to push it down. 😉

  • Aleksandar

    Hi Klaus,
    I would say that Filtering is happening because of computed column(LineTotal) that exists in Sales.SalesOrderDetail table.
    Regards
    Aleksandar

    • Klaus Aschenbrenner

      Hello Aleksandar,

      Thanks for your comment.
      Yes, a Computed Column in a table prevents the Predicate Pushdown and introduces an explicit Filter operator in the execution plan.

      Thanks,

      -Klaus

  • James Lupolt

    Did this technique (getting a residual predicate instead of a filter iterator) solve a problem for you? In testing, both queries read the whole index. I understand the 2nd one can be more efficient than the first because of the residual predicate pushdown.

    Regarding locks, my test showed that the first query got a table lock and the second query got a page lock on every page in the index. It’s interesting that the locking strategy changed, but I’m not sure that the second case would be an improvement over the first.

  • James Lupolt

    Actually I think I see what you mean now. Under repeatable read, in the secondary query, the page locks are only intent-shared, and the key locks are released while the statement runs as rows are disqualified.

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top