Sql-server – 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:

<event name="lock_escalation" package="sqlserver" timestamp="2017-06-08T18:00:23.590Z">
  <data name="resource_type">
    <type name="lock_resource_type" package="sqlserver"></type>
    <value>12</value>
    <text><![CDATA[HOBT]]></text>
  </data>
  <data name="mode">
    <type name="lock_mode" package="sqlserver"></type>
    <value>5</value>
    <text><![CDATA[X]]></text>
  </data>
  <data name="owner_type">
    <type name="lock_owner_type" package="sqlserver"></type>
    <value>1</value>
    <text><![CDATA[Transaction]]></text>
  </data>
  <data name="transaction_id">
    <type name="int64" package="package0"></type>
    <value>10067714031</value>
  </data>
  <data name="database_id">
    <type name="uint32" package="package0"></type>
    <value>8</value>
  </data>
  <data name="lockspace_workspace_id">
    <type name="ptr" package="package0"></type>
    <value>0x0000004e491a1e50</value>
  </data>
  <data name="lockspace_sub_id">
    <type name="uint32" package="package0"></type>
    <value>4</value>
  </data>
  <data name="lockspace_nest_id">
    <type name="uint32" package="package0"></type>
    <value>1</value>
  </data>
  <data name="resource_0">
    <type name="uint32" package="package0"></type>
    <value>38609</value>
  </data>
  <data name="resource_1">
    <type name="uint32" package="package0"></type>
    <value>256</value>
  </data>
  <data name="resource_2">
    <type name="uint32" package="package0"></type>
    <value>0</value>
  </data>
  <data name="escalation_cause">
    <type name="lock_escalation_cause" package="sqlserver"></type>
    <value>0</value>
    <text><![CDATA[Lock threshold]]></text>
  </data>
  <data name="object_id">
    <type name="int32" package="package0"></type>
    <value>1052582838</value>
  </data>
  <data name="hobt_id">
    <type name="uint64" package="package0"></type>
    <value>72057596568207360</value>
  </data>
  <data name="escalated_lock_count">
    <type name="uint32" package="package0"></type>
    <value>6248</value>
  </data>
  <data name="hobt_lock_count">
    <type name="uint32" package="package0"></type>
    <value>6247</value>
  </data>
  <data name="statement">
    <type name="unicode_string" package="package0"></type>
    <value><![CDATA[(@accountId int, @date int, @basisId int)DELETE FROM StatFxLotCumulativeActivity WHERE accountId = @accountId AND reportDate > @date]]></value>
  </data>
  <data name="database_name">
    <type name="unicode_string" package="package0"></type>
    <value><![CDATA[aws_db]]></value>
  </data>
</event>

When I query the system views for the object_id/hobt_id pair referenced in that event:

select * from sys.tables where object_id = 1052582838
select * from sys.partitions where hobt_id = 72057596568207360

It seems clear to me that the table involved is partitioned and has a clustered index:

name                        object_id  principal_id schema_id parent_object_id type type_desc  create_date             modify_date             is_ms_shipped is_published is_schema_published lob_data_space_id filestream_data_space_id max_column_id_used lock_on_bulk_load uses_ansi_nulls is_replicated has_replication_filter is_merge_published is_sync_tran_subscribed has_unchecked_assembly_data text_in_row_limit large_value_types_out_of_row is_tracked_by_cdc lock_escalation lock_escalation_desc is_filetable is_memory_optimized durability durability_desc
--------------------------- ---------- ------------ --------- ---------------- ---- ---------- ----------------------- ----------------------- ------------- ------------ ------------------- ----------------- ------------------------ ------------------ ----------------- --------------- ------------- ---------------------- ------------------ ----------------------- --------------------------- ----------------- ---------------------------- ----------------- --------------- -------------------- ------------ ------------------- ---------- ---------------
StatFxLotCumulativeActivity 1052582838 NULL         1         0                U    USER_TABLE 2015-02-19 10:24:19.463 2017-02-21 14:06:03.857 0             0            0                   0                 NULL                     10                 0                 1               0             0                      0                  0                       0                           0                 0                            0                 0               TABLE                0            0                   0          SCHEMA_AND_DATA

partition_id      object_id  index_id partition_number hobt_id           rows      filestream_filegroup_id data_compression data_compression_desc
----------------- ---------- -------- ---------------- ----------------- --------- ----------------------- ---------------- ---------------------
72057596568207360 1052582838 1        32               72057596568207360 982143818 0                       2                PAGE

The table has 126 partitions, but only 13 have any rows. Running this query:

select * from sys.partitions where object_id = 1052582838 and rows > 0

Gives me this:

partition_number hobt_id              rows
---------------- -------------------- --------------------
18               72057596141502464    34861
21               72057596141699072    89201
22               72057596141764608    573135
23               72057596141830144    471881
24               72057596141895680    1776464
26               72057596142026752    9463538
25               72057596299771904    1454117
27               72057596359606272    3748776
28               72057596360327168    2650215
29               72057596361048064    2700981
30               72057596361179136    448102594
31               72057596361310208    1375155391
32               72057596568207360    998318651

Here's the SSMS-generated DDL for the table:

CREATE TABLE [dbo].[StatLotCumulativeActivity](
    [accountId] [int] NOT NULL,
    [reportDate] [int] NOT NULL,
    [awsId] [int] NOT NULL,
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [value] [bigint] NOT NULL,
    [activityType] [int] NOT NULL,
    [balanceId] [int] NOT NULL,
    [sourceOriginalTransactionId] [int] NULL,
    [basisId] [int] NOT NULL,
    [sourceSecurityId] [int] NULL,
 CONSTRAINT [PK_dbo_StatLotCumulativeActivity] PRIMARY KEY CLUSTERED 
(
    [accountId] ASC,
    [reportDate] ASC,
    [awsId] ASC,
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Best Answer

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] = <reported_hobt_id>;

Some additional resources:

Best Answer

Related Solutions

Sql-server – SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

Sql-server – Take action from extended event

Related Question