Table Lock Escalation - Performance Questions

Question

I have few questions on table lock escalations, please confirm or disprove:

1. Index Scan operator in a query results in Table Lock Escalation ? always or not (small tables) ?

2. When Shared lock is escalated to a table level, it blocks small updates and deletes ? inserts as well ?

3. Is it possible to get counts of table lock_escalation event, grouped by login and then by table ? For example:

login1 - table1 - 11
login1 - table2 - 5
login2 - table1 - 29
etc...

Extended Events seemed to allow only one level of grouping if I am not mistaken

Answer 1

I will preface by saying: a Table Lock is generally taken only when the number of locks on a single table hits 5,000, or 40% of lock memory has been used. See the documentation for further details.

With that in mind:

1. An Index Scan does not necessarily take a table lock, it will only do so once it hits the thresholds. This may not happen even on a large table, if there is a row-goal on the scan (for example TOP or EXISTS). The total size of the table appears immaterial, only the number of rows actually read.
2. An S lock on the Table level is not compatible with an IX or an X lock, one of which would be attempted by DML such as UPDATE or DELETE. However, a U lock can still be taken, which means that DML can still find rows to modify, but will have to wait to actually attempt the modification.