HOBT vs OBJECT in Extended Event lock_escalation resource_type field

I'm capturing the lock_escalation event on our production servers (running the Enterprise Edition of SQL Server 2014) and trying to make sense of the output, particularly the resource_type field. Although there are 17 different possible values for the resource_type field, all I see is OBJECT and HOBT. On some servers I only see OBJECT, but on one I see HOBT quite a lot.

What is SQL Server trying to tell me with that field? All of our tables, on all servers, are currently configured with LOCK_ESCALATION = TABLE, which we are considering changing. The server where I see HOBT so often has many more partitioned tables than our other servers, but, from what I understand and given our configuration, SQL Server should only be escalating to the table level and not to the partition level.

I am quite certain that all of our tables are configured with LOCK_ESCALATION = TABLE. I ran this query in the database where I am seeing so many HOBT results:

SELECT lock_escalation_desc, total = COUNT(*)
    FROM sys.tables
    GROUP BY lock_escalation_desc

And I got one row:

lock_escalation_desc  total
TABLE                 143

(That particular database is so enormous that it basically gets its own server.)

EDIT: I'm pretty sure now that OBJECT indicates a table lock and HOBT indicates a partition-level lock. But I am very surprised to see the partition-level locking, because the tables involved are not configured to be locked that way, and everything I've read indicates that SQL Server should be locking at the table level.

Here is the full XML for one of the events:

```



12




5




1




10067714031



8



0x0000004e491a1e50



4



1



38609



256



0



0




1052582838



72057596568207360



6248



6247

You shouldn't need to guess at this. According to the documentation for sys.dm_tran_locks (under "Resource Details"), it shows that:


HOBT

means:


Represents a heap or a B-tree. These are the basic access path structures.

and the "resource_associated_entity_id" field (that should be there or should be added) value relates to:


HoBt ID. This value corresponds to sys.partitions.hobt_id.

Meaning, plug the "resource_associated_entity_id" value into:

-- run this in the DB where the object exists
SELECT OBJECT_SCHEMA_NAME(sp.[object_id]) AS [SchemaName],
       OBJECT_NAME(sp.[object_id]) AS [ObjectName],
       si.[name] AS [IndexName],
       *
FROM   sys.partitions sp
INNER JOIN sys.indexes si
        ON si.[object_id] = sp.[object_id]
       AND si.[index_id] = sp.[index_id]
WHERE  sp.[hobt_id] = ;

Some additional resources:

• Lock Escalation (Database Engine)

• Enabling Partition Level Locking in SQL Server 2008

• Lock:Escalation Event Class

StackExchange Database Administrators Q#175796, answer score: 4