SQL Server - A simple SELECT caused an index lock escalation. Why?

A few weeks ago, I made a short post here about the fact that a simple SELECT in the default isolation level can trigger an index lock escalation on a SQL Server.

This sparked a small discussion in the comments about how and why.?

That's why I promised to build and show a small demo. I will do so with this article.

I think it would be a good start from here if you briefly skim through the post (link is above).?

This demo can be especially interesting for you if you often see index lock escalation in sys.dm_db_index_opertional_stats() in your SQL Server environment and can't explain it.?

Since I can't work with my customers' data here, of course, I use the sample tables from this excellent article on prefetching by Fabiano Amorim to follow along. This article is also an excellent basis to further follow our example and explain some interesting background to it.

It is essential to understand that Fabiano details prefetching but not our strange locking case. Therefore, I will modify this a little bit.

First, please create your database and the two tables described in the Fabiano article: TestTab1 and TestTab2.

Then we also use Fabiano's example query but now use this in a stored procedure:

CREATE OR ALTER PROC p2
(
@p1 decimal(18,2)
)
as
SELECT TestTab1.Col4, TestTab2.Col1
? FROM TestTab1?
?INNER JOIN TestTab2
? ? ON TestTab1.ID = TestTab2.ID_Tab1
?WHERE TestTab1.Col4 < @p12
        

Why a procedure? We want to play with parameter sniffing, and procedures are the most fun.?

Now we can create a plan with the first execution and put it into the plan cache.?

CHECKPOINT -- for explanations, please consider the blog post above!
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
EXEC p2 0.8        

As we can see in the execution plan, both nested loops use a prefetch.

We note briefly, with the help of sys.dm_db_index_operational_stats, how many index escalations our table had already. Please forgive me that we don't start at 0 here; of course, I tried this before.?We note the 22.?

SELECT object_name(object_id), index_lock_promotion_attempt_count, index_lock_promotion_count, *
FROM sys.dm_db_index_operational_stats(db_id(), object_id('TestTab2'), null, null)        

We run our procedure again, but of course, with a substantially different parameter. Here is the 800.?

exec p2 800        

And already, we see that our TestTab2 had one more index escalation. Not an easy life has this table.?23! Oops, it's always 23, right? ;)

If you are instead a fan of other tools for viewing locks, then here is sp_lock as an example.?

sp_lock 66 -- my SPID in my environment; change it! :)        

As you can see here, it is no longer possible to modify rows (update/delete) while the query is running.?

As described in my post linked above, we have already found this issue in several customer databases and applications. This is usually not a problem in smaller environments, but if the environment is large enough, especially with many users with a massive OLTP workload, this has unfortunately negative side effects.?

Thanks for following me so deep down the SQL Server rabbit hole.?